Formula to Count the Name Once

Good Day All:


Does anyone know how to create a Count Formula to Count the Name just once Based on the Criteria?


For Example: Count the Name (Assignee Column) Once in Sprint 5 (Sprint Column):

John Doe

John Doe

John Doe

Jane Doe

Jane Doe

Jane Doe

Therefore, my total count should be 2 (John Doe and Jane Doe).


Thanks

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Ray B The OR shouldn't be in there at all.

    =COUNT(DISTINCT(COLLECT({Name}, {Sprint Range 1}, "ACAMS 2.0 SF - Sprint 5")))


    @Genevieve P The problem with using the COUNTIFS/DISTINCT is that the DISTINCT function only allows for a range. Not a range/criteria set.

    If you wanted to count 1 for a specific name or set of specific names, you would want to use an IF/COUNTIFS combo to say

    =IF(COUNTIFS({Range}, "Name 1")>0, 1) + IF(COUNTIFS({Range}, "Name 2")> 0, 1) + .........................


    So that if there is a count of that name, add 1.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!