2

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  | 
2
  • 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. Commented Aug 1, 2023 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. Commented Aug 1, 2023 at 21:38

2 Answers 2

0

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)
0

You can use COUNTIF to count the occurrences.

enter image description here

After that, copy the count column as values.

enter image description here

Last, remove the duplicates

enter image description here

enter image description here

2
  • 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.
    – Blindspots
    Commented Sep 3, 2023 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
    Commented Sep 3, 2023 at 3:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.