Thursday, August 22, 2013

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 NameAgent SupervisorPhone Number
Devan Nicolette555-555-0123
Devan Nicolette555-555-0124
DavidAmie 555-555-0125
Brooke Jeff555-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:
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,

No comments:

Post a Comment