1

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”.

2

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".

  • 1
    Side remark: use Senseful has a nice tool for formatting tables in Stack Exchange posts. – user79865 Jun 25 '15 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.