SUMPRODUCT Problem

[WildWill]

Column D = text values (client names) where the same name could appear more
than once in the column and where there could also be blank rows.

Column H = contains "Success" or "Failure".

I want to count the number of UNIQUE clients where I have had "Success" as
an outcome, as in the example below, where the answer would be 2:

Column D Column H
AlphaX Success
AlphaX Success
DeltaD Failure
CharlieW Sucess

 

[Mike H]

Hi,

Try this array formula

=COUNT(1/FREQUENCY(IF(B$1:B$100="Success",MATCH(A$1:A$100,A$1:A$100,0)),ROW(A$1:A$100)-ROW(A$1)+1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[WildWill]

Thanks, worked 100%

Hi,

Try this array formula

=COUNT(1/FREQUENCY(IF(B$1:B$100="Success",MATCH(A$1:A$100,A$1:A$100,0)),ROW(A$1:A$100)-ROW(A$1)+1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike