Google Spreadsheet Counting Duplicates to find most common values

Question:

( by Brian Perkins )

Hi, so I have a question about sheets.

What I would like to do is to create cells to count the number of times a supervisor's name shows up on the spreadsheet, and then display that number. This way, I could find out which supervisor appears most commonly in my spreadsheet. I can do it easily by using "ctrl+f" and just manually counting, but I'd rather have the value be generated with a formula that I could share with my coworkers, so that way at the bottom of the spreadsheet, I could generate a graph of that data. I hope I've worded this question in a way that makes sense. Thanks for looking and I appreciate the help.

Agent Name	Agent Supervisor	Phone Number
Devan	Nicolette	555-555-0123
Devan	Nicolette	555-555-0124
David	Amie	555-555-0125
Brooke	Jeff	555-555-0126

Solution:

Have a look at the following screenshot of "Sheet1":

I have the following formula in cell E1:
=query(A:C;"select B,count(A) where B<>'' group by B label count(A) 'Count' ";1)

And if you want to use the above formula in another sheet, then try the following formula:

=query('Sheet2'!A:C;"select B,count(A) where B<>'' group by B label count(A) 'Count' ";1)

And if you want it to sort it, so that Supervisor appearing most number of times should be showed up, then try this:

=query(A:C;"select B,count(A) where B<>'' group by B order by count(A) desc label count(A) 'Count' ";1)

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive