Count Distinct from multiple criteria?

Charlene Dewbre · 02/02/21

I have a data set similar to this sample which contains:

Primary column with unique names
Visited dates column formatted as DATE
ClientID column - this has a many to one relationship with the Primary (Sandra and Mike can both work for the same Client)
Results of the visit: Yes, Maybe
Product discussed

I'm trying to get a count of, for Product A, the unique clients that said Yes or Maybe in a given date range?

Right now, I can get this far with a COUNTIFS:

=COUNTIFS([Date Visited]:[Date Visited], >DATE(2021, 1, 1), [Date Visited]:[Date Visited], <DATE(2021, 2, 28), [Possible]:[Possible], <> "", [Interested In]:[Interested In], "Product A")

Which returns 2 (all possible Product A's in a given range)

I think I need to use a COUNT(DISTINCT(COLLECT... to find out the unique # of clientIDs in that same data set, but nothing I'm trying is working right now.

Any ideas? I'm open to helper columns.

Charlene Dewbre · 02/02/21

My solution in case others need to count based on filtered results:

A helper sheet.

I created an automation that runs on the date I need to collect the data.

1 Set conditions to create my filter set:

Condition 1 - date range
Condition 2 - Possible column selections
Condition 3 - Product choice

2 Copy rows to "New Sheet"

3 Use a COUNT(DISTINCT(...)) on the new sheet to count the orgs.

Generate a summary report or dashboard, if necessary, to view the info together.

If anyone can figure out a way to do this with a formula on the main sheet, I'd love to hear it. :)

Thanks!

Charlene Dewbre · 02/02/21