Google-sheets – Count unique values in second column corresponding to unique value in first column

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

I have a Google sheet with cities corresponding to countries like this. Notice that neither countries nor cities are unique and there can be duplicates.

|    A    |      B     |
------------------------
| UK      | London     |
| UK      | Manchester |
| UK      | Manchester |
| UK      | Birmingham |
| Ireland | Dublin     |
| Norway  | Oslo       |
| Norway  | Trondheim  |

Now I want to count the number of unique cities for each unique country. The result should look like this.

|    C    | D |
---------------
| UK      | 3 |
| Ireland | 1 |
| Norway  | 2 |

I got column C using UNIQUE(A1:A). But how do I get column D?

Best Answer

=QUERY(UNIQUE(A1:B), 
 "select Col1,count(Col2) 
  where Col1 is not null 
  group by Col1
  label count(Col2)''", 0)

=ARRAYFORMULA(IFERROR(VLOOKUP(C:C, QUERY(UNIQUE(A1:B), 
 "select Col1,count(Col2) 
  where Col1 is not null 
  group by Col1
  label count(Col2)''", 0), 2, 0)))