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

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.

--
Biff
Microsoft Excel MVP


"Rolf" <(E-Mail Removed)> wrote in message
news:68546660-F3D4-4E1A-AEB9-(E-Mail Removed)...
>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%)?