Google Spreadsheet -How to count unique values based on 2 columns with criteria from other columns?

Question

I have the following data: https://docs.google.com/spreadsheets/d/16JyE91jVJ8CgLfBhlygQCA85xJs5cjQLojM6b318JNQ/edit#gid=0

In a single cell, I want to get the number of unique Countries & Cities with Status = "PENDING" and Special Package = "Yes" with out creating a new column. I tried using the following formula:

=COUNTUNIQUEIFS(ARRAYFORMULA(A2:A&B2:B),D2:D,"PENDING",E2:E,"Yes")

But it returns the error: Argument must be a range.

Kessy Kessy · Accepted Answer · 2020-06-03T09:22:41

You have to use it like this:

=COUNTUNIQUEIFS(A2:A7,D2:D7 ,"PENDING", E2:E7, "Yes")

So you are saying:

Count from A column
Where D column = "PENDING"
And E column = "YES"

Check COUNTUNIQUEIF documentation.

Update

You can count the uniques with the B column since the countries will repeat in almost any circumstance, so you can count the unique cities that have the conditions.

Also you can use a query to group the results by country or city and even show you which one is:

=QUERY(A2:E7, "select count(B), B, A where D ='PENDING' and E ='Yes' GROUP BY A,B")

Google Spreadsheet -How to count unique values based on 2 columns with criteria from other columns?

1 Answers