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

1 Answer 1

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
  • 1
    Side remark: use Senseful has a nice tool for formatting tables in Stack Exchange posts.
    – user79865
    Jun 25, 2015 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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