Counting Unique Values Based on Request Type.

I am trying to use a COUNTIFS formula to count the total number of unique Request IDs with the Request Type as "Other" on a separate sheet. I keep getting a value of "0" when it should be "2". Any help is appreciated!

The formula I am using is: =COUNTIFS({Completion Updates Range 7}, ="Other", {Completion Updates Range 10}, COUNT(DISTINCT({Completion Updates Range 10})))

Completion Updates Range 7 is the Type of Request column

Completion Updates Range 10 is the Request ID column.

Best Answer

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    edited 12/14/22 Answer ✓

    Hey @Brett Hughes , You're on the right track. We need to use COUNT DISTINCT COLLECT function combo...

    =COUNT(DISTINCT(COLLECT({Completion Updates Range 10}, {Completion Updates Range 7}, "Other")))

    So this says, count the distinct values of Request ID when Type of Request is Other.

    Here's a related video that might help too.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!