Count value in column 2, but if duplicate in column 1 only count once

[laraivison]

Hi,

I am trying to count how many times the values appear in column 2, but if the corresponding value in column 1 is a duplicate, I only want to count the value in column 2 once.

[TABLE="width: 200"]
<tbody>[TR]
[TD]Unit 1[/TD]
[TD]730[/TD]
[/TR]
[TR]
[TD]Unit 1[/TD]
[TD]730[/TD]
[/TR]
[TR]
[TD]Unit 2[/TD]
[TD]715[/TD]
[/TR]
[TR]
[TD]Unit 3[/TD]
[TD]730[/TD]
[/TR]
[TR]
[TD]Unit 3[/TD]
[TD]730[/TD]
[/TR]
[TR]
[TD]Unit 4[/TD]
[TD]730[/TD]
[/TR]
[TR]
[TD]Unit 5[/TD]
[TD]715[/TD]
[/TR]
[TR]
[TD]Unit 6[/TD]
[TD]715[/TD]
[/TR]
</tbody>[/TABLE]

so for the above data I would hope to get

715 = 3
730 = 3

Thanks in advance!

 

[PaddyD]

with your data in a1:b9 (including a header row), and 730 and 715 in d2 and d3 respectively:

=SUM(IF(FREQUENCY(IF($B$2:$B$9=D2,MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),1))

...entered with control + shift + enter, not just enter.

 

[laraivison]

Perfect. Thats exactly what I was looking for! Thanks!