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!