# How can I group/count occurences of a column

I have a Google sheet with 2 columns.

| Jim  | Foo |
| John | Bar |
| Jane | X   |
| Jim  | YZ  |


What I usually do is:

1. download the values as a CSV
2. write/run a script that processes the CSV
3. the script goes over each row, groups by the first column, and counts occurrences.
4. Then I output the values in descending order by number of occurrences.

How can I do this directly in Google Sheets using a formula?

The ordering of ties does not matter, so the expected output would be:

| Jim  |  2  |
| John |  1  |
| Jane |  1  |


or

| Jim  |  2  |
| Jane |  1  |
| John |  1  |

• Welcome to Web Applications. Did you know that google-sheets (the tag of this site) has thousands of questions and answers? Please spend some time searching the site. If you still need help, show the most relevant findings and explain why they don't meet your needs. Regarding your script, did you know that Google Sheets can be extended using Google Apps Script, which uses JavaScript as a programming language? This site also has many questions about using Google Apps Script to extend Google Sheets.
– Rubén
Aug 1 at 21:34
• Your local script might use the Google Sheets API, but questions about how to use this API are off-topic here. They are on-topic in Stack Overflow and probably on other Stack Exchange sites.
– Rubén
Aug 1 at 21:38

## 2 Answers

Do it all in QUERY:

=QUERY(A:B, "select A, count(B)
where A is not Null group by A
order by count(B) desc
label count(B) ''")


OR, combine SORT and QUERY:

=SORT(
QUERY(A:B, "select A, count(B)
where A is not Null group by A
label count(B) ''"),2,0)


You can also MAP the values into a LAMBDA. This version assumes values are always in A1:B1 pairs so the 2nd column is ignored. Also uses UNIQUE, TOCOL, COUNTIF:

=SORT(
MAP(UNIQUE(TOCOL(A:A,1)), LAMBDA(a,
{a,COUNTIF(A:A,a)})),
2,0)


This one only increments if there is a value in column B by swapping in COUNTIFS:

=SORT(
MAP(UNIQUE(TOCOL(A:A,1)), LAMBDA(a,
{a,COUNTIFS(A:A,A,B:B,"<>"&"")})),
2,0)



Same as previous but uses FILTER and ROWS instead:

=SORT(
MAP(UNIQUE(TOCOL(A:A,1)), LAMBDA(a,
{a,ROWS(FILTER(B:B,A:A=a))})),
2,0)


You can use COUNTIF to count the occurrences.

After that, copy the count column as values.

Last, remove the duplicates

• The OP wants to know how to "do this directly in Google Sheets using a formula". Your approach involves "multiple" formulas, and then multiple additional steps. Your answer would be improved and better reflect the question if you edited it down to a single formula. Sep 3 at 1:21
• As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
– Community Bot
Sep 3 at 3:40