Formula to Count the Name Once

Anthony Barthelemy

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

Paul Newcome

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

Paul Newcome

Try a COUNT/DISTINCT

=COUNT(DISTINCT(Assignee:Assignee))

Anthony Barthelemy

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.

Paul Newcome

My apologies. I missed the additional criteria.

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

Anthony Barthelemy

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?

Paul Newcome

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

Anthony Barthelemy

Yes, I can Share the Sheet with you.

https://app.smartsheet.com/sheets/mwW538Xp7MFg9cqQwJ8HJmjP8JvmxM4h3HfWV8R1?view=grid

Paul Newcome

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

Anthony Barthelemy

Use the Request and I can grant access

Anthony Barthelemy

Did you get access?

Genevieve P.

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

Paul Newcome

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

Genevieve P.

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

Paul Newcome

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

Anthony Barthelemy

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

Paul Newcome

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