CountIfs with criteria for location and month, return number of distinct users

Has anyone figured out how to use Countifs to define 2 ranges and criteria, then have the result be a number of distinct names (3rd column)

I have read many of the posts, but I don't see anything that shows how to do this.

Assume I have 2000 lines of data, I need to know the total number of distinct colleagues for the CTC within the month specified.

CTC - 301-Renton

Month - Jan

#Distinct Colleagues = ?

Answers

  • Shimanta Roy
    Shimanta Roy ✭✭✭✭

    To find the number of distinct colleagues, try the following formula:

    =COUNT(DISTINCT( COLLECT(ID:ID, CTC:CTC, "301-Renton", Month:Month, "Jan")))

    I suggest you to create two dropdown fields in the sheet summary for CTC and Month. So that youcan change the values anytime and find the corresponding counts. If you create those fields in sheet summary then use the following formula to get the count:

    =COUNT(DISTINCT( COLLECT(ID:ID, CTC:CTC, CTC#, Month:Month, Month#)))

    Assuming, the sheet summary fields are named as CTC and Month.

    Senior Business Intelligence Analyst
    Augmedix Bangladesh
    (A Commure Company)
    shimanta@augmedix.com

  • Becky Wilson
    Becky Wilson ✭✭✭✭✭

    Returns a value of 0. Also not sure how ID:ID gets me colleague data so I used Colleague:Colleague instead

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭

    Hello @Becky Wilson

    =COUNT(DISTINCT( COLLECT(Colleague:Colleague, CTC:CTC, "301-Renton", Month:Month, "Jan")))

    should work.

    If it returns "0" value, can you share a screenshot of your formula and where you put it?

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!