Count / Distinct - Total of unique values based on logic.



I have a sheet that holds vendor and contract information. Each vendor can have multiple contracts (rows).

3 of the columns are "Vendor" and "Contract Status" and "Contract Type".

I have a separate metric sheet.

In the metric sheet, using ranges, I want to be able to show:

  • The number of unique / distinct vendors.
    • I can do this with the formula =COUNT(DISTINCT({Contracts - Vendor}))
  • What I want to do is count the the number of vendors that where "Contract Status" = "Active" and where "Contract Type" = "SFO".

So in the table example below. There would be 4 Vendors with "Active" and "SFO" contracts.

Any help would be great.


Best Answer