Formula to Count the Name Once
![Anthony Barthelemy](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
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.
-
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. 🤣
-
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!