How do I count once, multiple duplicate values?
 

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%)?

How do I count once, multiple duplicate values?
 

Try one of these array formulas** :

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

=COUNT(1/FREQUENCY(IF((D2:D5=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((D2:D5=50)*(A2:A5<""),MATCH(A2:A5,A 2: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.

--
Biff
Microsoft Excel MVP


"Rolf" wrote in message
...
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%)?