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

Head Scratch

Hi,

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.

Thanks.

Head Scratch

Hi Nick,

Thanks very much, It now works.

HS

Nick Korna

HI @Head Scratch,

This formula should do what you're after, I think:

=COUNT(DISTINCT(COLLECT(Vendor:Vendor, [Contract Status]:[Contract Status], "Active", [Contract Type]:[Contract Type], "SFO"))).

Hope this helps, and if something is amiss or you've any questions etc. then just post! 😊

Head Scratch

Hi Nick,

Thanks very much, It now works.

HS