K
KeLee
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
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