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

@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.
Answers

Try a COUNT/DISTINCT
=COUNT(DISTINCT(Assignee:Assignee))

Close! The number should be 3 but my formula returns only 1. The Names occur multiple times
My Formula:
=COUNTIFS(DISTINCT({Name}, OR(@cell = "Ron", @cell = "Shobhit", @cell = "Sriram"), {Sprint Range 1}, "ACAMS 2.0 SF  Sprint 5"))
My Objective is to Count the number of Developers in a given Sprint. Therefore, I only want to count there name once when it appears in the Sprint.

My apologies. I missed the additional criteria.
=COUNT(DISTINCT(COLLECT({Name}, {Sprint Range 1}, "ACAMS 2.0 SF  Sprint 5")))

Thanks that corrected the formula but I am still getting 1 as a result. I have 3 Developers for Sprint 5. Therefore, my results should be 3 and not 1. Maybe Distinct is not the right formula to use to count the number of developers in a given sprint, without counting the same name twice. Your thoughts?

Are you able to provide a screenshot? It should be working. I have used it successfully many times.

Yes, I can Share the Sheet with you.
https://app.smartsheet.com/sheets/mwW538Xp7MFg9cqQwJ8HJmjP8JvmxM4h3HfWV8R1?view=grid

It is saying that I do not have access to the item.

Use the Request and I can grant access

Did you get access?

Hi @Ray B & @Paul Newcome
Could it be the OR that is confusing things, here?
Try adding 3 COUNTIFS together, one for each name, instead of using OR:
=COUNTIFS(DISTINCT({Name}, "Ron", {Sprint Range 1}, "ACAMS 2.0 SF  Sprint 5")) + COUNTIFS(DISTINCT({Name}, "Shobhit", {Sprint Range 1}, "ACAMS 2.0 SF  Sprint 5")) + COUNTIFS(DISTINCT({Name}, "Sriram", {Sprint Range 1}, "ACAMS 2.0 SF  Sprint 5"))
Keep in mind that the names will need to be exactly what's written "in these" for it to count. Let me know if this works!
Cheers,
Genevieve

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

Oops, thanks for catching this, Paul! Need more coffee today... ☕️

@Genevieve P I have already had 3 cups and it is only a little after 8 in the morning here. I'm pretty sure I now know how a cat would feel around a disco ball. 🤣

Ok the Count worked but I am trying to add the Criteria to only Count Sprint 5 Names Once
Added Criteria:
{Sprint Range 1}, "Sprint 5"
Should that be at the beginning of the formula or Plus after all the names?
Thanks

@Ray B It is going to depend on which formula you are using. If you are adding the IF/COUNTIFS formulas together, then the range/criteria set would go within each of the COUNTIFS functions.
Help Article Resources
Categories
Check out the Formula Handbook template!