How can I group/count occurences of a column

Question

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  | 

Answer 1

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)

Answer 2

You can use COUNTIF to count the occurrences.

After that, copy the count column as values.

Last, remove the duplicates