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

[Robmeister89]

So I have the following:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]Team1[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]Team2[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[TD]Team3[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]Team3[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]Team2[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD]Team4[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]92[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7[/TD]
[TD]Team1[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]82[/TD]
[/TR]
</tbody>[/TABLE]


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