How do I count once, multiple duplicate values?

[Rolf]

I have the following sheet

Container sub-Container Volume %used
Name1 Sub1 100 60
Name1 Sub1 100 80
Name2 Sub2 100 30
Name3 Sub3 100 40

I want to count the Container column that exceeds 50%, but NOT duplicate
values (so in the above example I should get 1 as the answer (Name1 is only
counted ONCE even though there are two occurences of it exceeding 50%)?

 

[T. Valko]

Try one of these array formulas** :

Assuming there won't be any empty cells in the Container range:

=COUNT(1/FREQUENCY(IF((D25>=50,MATCH(A2:A5,A2:A5,0)),ROW(A2:A5)-ROW(A2)+1))

If there might be empty cells in the container range:

=COUNT(1/FREQUENCY(IF((D25>=50)*(A2:A5<>""),MATCH(A2:A5,A2:A5,0)),ROW(A2:A5)-ROW(A2)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.