Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 05, 12:58 PM posted to microsoft.public.excel.worksheet.functions
KeLee
 
Posts: n/a
Default 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


  #2   Report Post  
Old December 9th 05, 01:28 PM posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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

  #3   Report Post  
Old December 9th 05, 02:25 PM posted to microsoft.public.excel.worksheet.functions
KeLee
 
Posts: n/a
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using sumproduct to count number by date JerryS Excel Worksheet Functions 2 June 6th 05 10:37 PM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 8 May 18th 05 04:23 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM
SUMPRODUCT Formula to Count Row of data Below Matched Criteria Sam via OfficeKB.com Excel Worksheet Functions 8 February 3rd 05 02:37 AM
adding two sumproduct formulas together ski2004_2005 Excel Worksheet Functions 1 November 12th 04 10:08 PM


All times are GMT +1. The time now is 06:42 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017