# Distinct CountIFS Statement

I need a little assistance...

I was able to get the formula to partially work using =count(distinct but, it was not taking the name cell into account and returned the same number in each row.

I need a distinct count of PrAbv based on P Name.

I got "#inccorect argument" when I changed count to countifs. I'm sure it's something simple I'm missing here.

=COUNTIFS(DISTINCT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "B")) + COUNTIFS(DISTINCT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "U")) + COUNTIFS(DISTINCT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "A")) + COUNTIFS(DISTINCT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "C")) + COUNTIFS(DISTINCT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "P"))

Tags:

• In that case you would need something more like

=COUNT(DISTINCT(COLLECT({Plan}, {Name}, @cell = "x")))

• You need to use a COUNT/DISTINCT/COLLECT.

• I'm still getting a total in the sheet versus what is actual for the Name.

=COUNT(DISTINCT(COLLECT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "B")))+rest

Returns 5 when it should return 4.

Now, I did notice if I use the statement without adding the additional statements, it returns 1 which is correct.

• what exactly do you mean by the "+rest" portion above?

• Sorry, Paul for the late response. Just getting back to this project.

+rest was adding the addition statements for "U, A, C, and P". Essentially, duplicating the statement. =COUNT(DISTINCT(COLLECT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "B"),+ COUNT(DISTINCT(COLLECT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "C"),+ COUNT(DISTINCT(COLLECT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "U") etc...

I need a distinct count to remove the duplicated items.

• I'm still not sure I follow what you are trying to accomplish. Are you wanting a count of distinct entries in the Plan column based on the Name column being equal to "x"?

• Correct.

x = 4

y=2

z= 5

total count 11

• In that case you would need something more like

=COUNT(DISTINCT(COLLECT({Plan}, {Name}, @cell = "x")))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!