Count Distinct from multiple criteria?

Options
Charlene Dewbre
edited 02/02/21 in Formulas and Functions

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

  1. Primary column with unique names
  2. Visited dates column formatted as DATE
  3. ClientID column - this has a many to one relationship with the Primary (Sandra and Mike can both work for the same Client)
  4. Results of the visit: Yes, Maybe
  5. 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.

Best Answer

  • Charlene Dewbre
    edited 02/02/21 Answer ✓
    Options

    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!

Answers

  • Charlene Dewbre
    edited 02/02/21 Answer ✓
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!