Google-sheets – Count the unique values in a Column based on criteria in 2 different columns

google sheets

I'm working in Google Sheets. I have 3 columns:

ID     Region    Year
101      US      2014
102      EU      2014
103      US      2014
102      US      2015
102      EU      2014

I would like to count the number of Unique IDs for each year and region. Thus, the results would be:

2014 US: 2
2014 EU: 1
2015 US: 1

Here are the limitations I have to work with:

  • I cannot use a Pivot
  • I cannot create a separate column to first extract the unique values, and then count those.

I've been trying to come up with a formula using IF and COUNTUNIQUE, but for some reason, it doesn't seem to be working.

Best Answer

You can apply query directly to the output of unique without creating any extra columns in the sheet. The unique command, applied to a range, returns distinct rows, thus eliminating repeated Ids. 

=query(unique(A:C), "select Col3, Col2, count(Col1) where Col1 is not null group by Col2, Col3", 1)

The columns are referred to abstractly as Col1, Col2, Col3 because the query operates on an array contained in memory, not in the sheet.

Output: 

Year    Region  count ID
2014    EU      1
2014    US      2
2015    US      1
Related Topic