# Formula to Count the Name Once

Options
✭✭✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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

«1

• ✭✭✭✭✭✭
Options

Try a COUNT/DISTINCT

=COUNT(DISTINCT(Assignee:Assignee))

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

My apologies. I missed the additional criteria.

=COUNT(DISTINCT(COLLECT({Name}, {Sprint Range 1}, "ACAMS 2.0 SF - Sprint 5")))

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Use the Request and I can grant access

• ✭✭✭✭✭✭
Options

Did you get access?

Options

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

• ✭✭✭✭✭✭
Options

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

Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Ok the Count worked but I am trying to add the Criteria to only Count Sprint 5 Names Once

{Sprint Range 1}, "Sprint 5"

Should that be at the beginning of the formula or Plus after all the names?

Thanks

• ✭✭✭✭✭✭
Options

@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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!