Google-sheets – Google Sheets Count Unique value with multiple criteria

google sheets

I am working on a Google spreadsheet as below:

Walkin date | Name    | Phone | Status | Remarks

01/06/15    | Client1 | 1001  | Open   | Will join soon
03/06/15    | Client2 | 2002  | Sales  | Enrolled
04/06/15    | Client3 | 3003  | Sales  | Enrolled
07/06/15    | Client2 | 2002  | Sales  | Enrolled

Everything else working fine, just could not figure out this very formula which should calculate the number of Unique Sales, that is in this case: 2, and NOT 3.
In other words, the condition I am not able to create is: Count number of unique values (Column 3 [Phone] ) where Status (Column 4) is “Sales”.

Best Answer

Use the formula: =COUNTIF(UNIQUE(C:D),"Sales")

How it works:

Unique(C:D) will remove any rows from those columns that are duplicated, resulting in:

Phone | Status 
-------|-------
1001   | Open 
2002   | Sales 
3003   | Sales

The Countif portion looks inside those resulting duplicate values and counts every instance of "Sales".

Related Topic