1

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?

1

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

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

0

2
  • 1
    Thanks, however this only works if I take column C from the query itself. If column C is already provided and I select only the output column D from the query, the order might have been mixed up so that the columns don't match anymore. You can try it yourself by writing only [..] select count(Col2) where [..].
    – philmcole
    Jun 27 '19 at 21:37
  • 1
    @philmcole answer updated
    – user0
    Jun 27 '19 at 21:46

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .