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

Try a COUNT/DISTINCT
=COUNT(DISTINCT(Assignee:Assignee))
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!

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")))
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!

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

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

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@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!

Oops, thanks for catching this, Paul! Need more coffee today... ☕️
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@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. 🤣
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!

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.
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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!