How to count uniques of a SUMPRODUCT subset?
 

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

How to count uniques of a SUMPRODUCT subset?
 

Try...

=SUM(IF(FREQUENCY(IF((AllStatus="Attended")*(AllSo urce="Mail")*(AllCusts<
""),MATCH(AllCusts,AllCusts,0)),ROW(AllCusts)-MIN(ROW(AllCusts))+1)0,1)
)

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

Hope this helps!

In article ,
"KeLee" wrote:

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

How to count uniques of a SUMPRODUCT subset?
 

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


"Domenic" wrote:

Try...

=SUM(IF(FREQUENCY(IF((AllStatus="Attended")*(AllSo urce="Mail")*(AllCusts<
""),MATCH(AllCusts,AllCusts,0)),ROW(AllCusts)-MIN(ROW(AllCusts))+1)0,1)
)

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

Hope this helps!

In article ,
"KeLee" wrote:

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