Count unique values in one column based on criteria in another column

[Robmeister89]

So I have the following:

A	B	C	D	E	F	G	H	I	J
5	1	Team1	...	...	...	...	...	...	80
5	2	Team2	...	...	...	...	...	...	95
5	3	Team3	...	...	...	...	...	...	88
5	4	Team3	...	...	...	...	...	...	82
5	5	Team2	...	...	...	...	...	...	84
5	6	Team4	...	...	...	...	...	...	92
5	7	Team1	...	...	...	...	...	...	82

<tbody>
</tbody>

Columns A-B and D-I don't matter in this instance so I just put ... in them for the most part. What I need to do is come up with a formula to count the unique values in Column C based on column J being less than 85. So the formula would output 3 unique values (Team1, Team2, and Team3 all have a number less than 85 in column J). Some cells in column J will be empty, not sure if that is going to effect the formula.

Any help with this would be greatly appreciated!

 

[Leandroarb]

Hi Robmeister89,

See if this is for you:

In cells: L1, M1, N1, O1 have the names of the teams:
Team1, Team2, Team3, Team4


In the cells: L2, M2, N2, O2 the formula:

=SUM(IF(($C$1:$C$7=L$1)*($J$1:$J$7>0)*($J$1:$J$7<85),1,0))

This is a matricial formula, you need press CTRL+SHIFT+ENTER for run ok?

 

[Domenic]

If you only want to return a unique count, in this case the number 3, try..

=SUM(IF(FREQUENCY(IF(LEN(J1:J7)>0,IF(J1:J7<85,IF(LEN(C1:C7)>0,MATCH(C1:C7,C1:C7,0)))),ROW(C1:C7)-ROW(C1)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Leandroarb]

If you only want to return a unique count, in this case the number 3, try..

=SUM(IF(FREQUENCY(IF(LEN(J1:J7)>0,IF(J1:J7<85,IF(LEN(C1:C7)>0,MATCH(C1:C7,C1:C7,0)))),ROW(C1:C7)-ROW(C1)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

No words... i'm a baby in this forum...
Thanks for this lesson.

 

[Robmeister89]

If you only want to return a unique count, in this case the number 3, try..

=SUM(IF(FREQUENCY(IF(LEN(J1:J7)>0,IF(J1:J7<85,IF(LEN(C1:C7)>0,MATCH(C1:C7,C1:C7,0)))),ROW(C1:C7)-ROW(C1)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Thank you, this worked perfectly!

 

[b4ell]

If you only want to return a unique count, in this case the number 3, try..

=SUM(IF(FREQUENCY(IF(LEN(J1:J7)>0,IF(J1:J7<85,IF(LEN(C1:C7)>0,MATCH(C1:C7,C1:C7,0)))),ROW(C1:C7)-ROW(C1)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hey Domenic,

This solution worked great for one criterion! How would you go about incorporating a second criterion from another column? For example, if you added a new column "K" and wanted K = "SpecificText" as another criterion.

I attempted to use an AND nested inside of the expression, but couldn't get it to work. I placed the AND like this:

....IF(And(J1:J7<85 , K1:K7 = "SpecificText")...


Could use your expertise! Thanks