Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using sumproduct to count number by date | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |