Combining Countifs & Distinct Formula

Options
bfridline
bfridline
edited 09/12/23 in Formulas and Functions

I am trying to combine countifs & distinct formula together but not having any luck. I need to get the count of requirements to 1 workshop (so many to 1). This means there are duplicates which I cannot count. Below is the formula I have tried just not having luck


=COUNTIF(DISTINCT({Business Requirement Document Range 3}){Business Requirement Document Range 1},"MDG_WKSP_04_01-Master Data Process Governance (Initiate) - Profit Center")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You will need to user a COUNT/DISTINCT/COLLECT combo instead.

    =COUNT(DISTINCT(COLLECT({Business Requirement Document Range 3}, {Business Requirement Document Range 1},"MDG_WKSP_04_01-Master Data Process Governance (Initiate) - Profit Center")))


    Basically we use the COLLECT function to pull together a list of cells from Range 3 based on our Range 1 range/criteria set. Then we wrap it in the DISTINCT function to filter out the duplicates. Then we wrap it in a COUNT function to get the count of what's left.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!