How to count uniques of a SUMPRODUCT subset?

G

Guest

Hello,
I have data in this format

A B C D
Status Source Name Cust #
-------- -------- ------- --------
Attended Mail James Brooks 10016-18
Attended Mail Woody Allen 10213-74
Attended Mail Steven Wright 10046-51
Attended Web Bill Hicks 10046-51
Attended Mail Richard Pryor 10046-51
Cancelled Mail Mort Sahl 10047-52

etc.

Each Column has a named dynamic range that defines it's area
e.g Column A is "AllStatus" defined by
=OFFSET(Sheet1!$A$1,0,0,COUNTA(MySheet!$A:$A),1)

I Have counted the number of people who attended an event that booked by
Mail using the formula:
=SUMPRODUCT(--(AllStatus="Attended"),--(AllSource="Mail"))

What I would now like to do is to find the number of customers that attended
that booked by mail.

Because a customer can have more than one person attend, as with customer
10046-51 above, I have a problem I can't solve.

The customer should count only once no matter how many people attended.
I am looking for the number of unique customers that had any person attend
by each status. In the example above that would be 3

I know I can count uniques using:
=SUMPRODUCT((AllCusts<>"")/COUNTIF(AllCusts,AllCusts&""))

But when i try to figure out the syntax to make this count uniques of the
subset meeting the 2 criteria ("Attended" and "Mail") I cannot make it work.

Any help is as always greatly appreciated from you lovely people who give
your time so kindly

Thankyou,
KeLee
 
D

Domenic

Try...

=SUM(IF(FREQUENCY(IF((AllStatus="Attended")*(AllSource="Mail")*(AllCusts<
""),MATCH(AllCusts,AllCusts,0)),ROW(AllCusts)-MIN(ROW(AllCusts))+1)>0,1)
Click to expand...
)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
G

Guest

Thank you so much Domenic
That works beautifully, I've just tried it out on my test data variables.
Now I'm off to try and understand it!

KeLee
 
Top