Count and sort in Google Sheets using QUERY()

When I’ve pulled some data into Google Sheets I often want a way to quickly chart the count of something, maybe it’s support tickets and I want to chart the incidence of particular tags or count tickets for each customer. In this situation I want a nice neat chart showing from ordered data.

Let’s say this is my data, and I want to see which are the most used names:

IDName
1Linda
2Ahmed
3Jenny
4Lena
5Ahmed
6Stef
7Simon
8Terry
9Terry
10Ahmed
11Linda
12James
13Stef
14James
15Steph
16Carol
17Alice
18Lucy
19Alice
20Stef

Previously I’ve been using =UNIQUE(Data!B2:B) to grab the unique names, then something like =COUNTIF(Data!B:B,D2) to count each row. This ends up with summary data which is not in order, so when I generate my chart it’s not as readable:

I mean it’s readable, but it’s not neat

Using the Google Sheets QUERY() function I can retrieve the data, count, and sort it at the same time: =QUERY(Data!A:B,"SELECT B, COUNT(B) WHERE B IS NOT NULL GROUP BY B ORDER BY COUNT(B) DESC", 1). Which gives us this much more pleasing chart:

The Google Sheet is here if you want to see it in action. Improvements welcome!

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.