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).


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.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!