| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Number of Duplicate Occurances
I have the following data set:
Month Item Salesperson Jan B AA Jan B AA Jan B AB Jan B AB Jan B AC Feb B AB Feb B AB Feb B AA I am trying to write a formula that will count the number of multiple sales by a salespersons that have had for Item B. In this example for Jan and Item B it would be two (Salesperson AA & AB both had multiple sales). Thanks for the help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Duplicate Occurances
=SUMPRODUCT(--(Month_Range="Jan"),--(Item_Range="B"),--(SalesPerson_Range="AA"))
will return the number of sales of B for sales person AA in Jan easier would be to use a list of all months, all sales people and all items, then refer to their cells instead of the hardcoded "Jan", "B" and "AA" Also I am assuming that the months are text and not formatted date numbers Regards, Peo Sjoblom "Scott Halper" wrote in message oups.com... I have the following data set: Month Item Salesperson Jan B AA Jan B AA Jan B AB Jan B AB Jan B AC Feb B AB Feb B AB Feb B AA I am trying to write a formula that will count the number of multiple sales by a salespersons that have had for Item B. In this example for Jan and Item B it would be two (Salesperson AA & AB both had multiple sales). Thanks for the help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Duplicate Occurances
Maybe something like this:
With your posted data in A1:C9 Then.... E1: Jan F1: B G1: =SUMPRODUCT(--(FREQUENCY((A2:A10&"_"&B2:B10=E1&"_"&F1)*MATCH(C2: C10&"",C2:C10&"",0),((A2:A10&"_"&B2:B10<E1&"_"&F1 )*ROW(A2:A10))+MATCH(C2:C10&"",C2:C10&"",0))*(A2:A 11&B2:B11<"")1)) Note_1: In case text wrap impacts the display, there are NO spaces in that formula. Note_2: Some of the references extend one cell below the potential end of the data. Does that help? *********** Regards, Ron XL2002, WinXP "Scott Halper" wrote: I have the following data set: Month Item Salesperson Jan B AA Jan B AA Jan B AB Jan B AB Jan B AC Feb B AB Feb B AB Feb B AA I am trying to write a formula that will count the number of multiple sales by a salespersons that have had for Item B. In this example for Jan and Item B it would be two (Salesperson AA & AB both had multiple sales). Thanks for the help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Duplicate Occurances
On Mar 29, 12:36 pm, Ron Coderre
wrote: Maybe something like this: With your posted data in A1:C9 Then.... E1: Jan F1: B G1: =SUMPRODUCT(--(FREQUENCY((A2:A10&"_"&B2:B10=E1&"_"&F1)*MATCH(C2: C10&"",C2:C*10&"",0),((A2:A10&"_"&B2:B10<E1&"_"&F 1)*ROW(A2:A10))+MATCH(C2:C10&"",C2:C1*0&"",0))*(A2 :A11&B2:B11<"")1)) Note_1: In case text wrap impacts the display, there are NO spaces in that formula. Note_2: Some of the references extend one cell below the potential end of the data. Does that help? *********** Regards, Ron XL2002, WinXP "Scott Halper" wrote: I have the following data set: Month Item Salesperson Jan B AA Jan B AA Jan B AB Jan B AB Jan B AC Feb B AB Feb B AB Feb B AA I am trying to write a formula that will count the number of multiple sales by a salespersons that have had for Item B. In this example for Jan and Item B it would be two (Salesperson AA & AB both had multiple sales). Thanks for the help.- Hide quoted text - - Show quoted text - Ron, I tried your formula, however in the last match function there seems to be something that is causing the formula to "#N/A" and i'm able to pinpoint it. I think that its the match type but i'm not sure. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Duplicate Occurances
Ron Coderre wrote...
Maybe something like this: With your posted data in A1:C9 Then.... E1: Jan F1: B G1: =SUMPRODUCT(--(FREQUENCY((A2:A10&"_"&B2:B10=E1&"_"&F1) *MATCH(C2:C10&"",C2:C10&"",0),((A2:A10&"_"&B2:B10 <E1&"_"&F1) *ROW(A2:A10))+MATCH(C2:C10&"",C2:C10&"",0))*(A2:A 11&B2:B11<"")1)) .... While I see the point behind your &"_"&, why not just use the direct approach? (A2:A10=E1)*(B2:B10=F1) It's shorter, and I strongly suspect multiple concatenations and one compare take more time than two compares and a multiply. Next, your MATCH(C2:C10&"",C2:C10&"",0) term is a bug in waiting. If there were any other item IDs in the Item column, e.g., if the sample data had been Month Item Salesperson Jan A AA Jan B AA Jan B AB Jan B AB Jan B AC Jan B AC Feb A AA Feb A AB your formula would return 3 rather than 2. Never assume OPs provide realistic sample data. A more robust formula would be =SUM(--(FREQUENCY(IF(($A$2:$A$9=$E$1)*($B$2:$B$9=$F$1), MATCH(IF(($A$2:$A$9=$E$1)*($B$2:$B$9=$F$1),$C$2:$C $9&"",0), $C$2:$C$9&"",0)),ROW($C$2:$C$9)-MIN(ROW($C$2:$C$9))+1)1)) which is an array formula, so need to hold down [Shift] and [Ctrl] keys before pressing the [Enter] key to enter it. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Duplicate Occurances
Create a helper column D
In D2: =COUNTIF($C$2:C2,C2)1 copy all the way down In E2: =IF(ISERR(SMALL(IF((Month="Jan")*(Item="B")*(Helpe r=TRUE),ROW(INDIRECT("1:"&ROWS(Month)))),ROWS($1:1 ))),"",INDEX(Salesperson,SMALL(IF((Month="Jan")*(I tem="B")*(Helper=TRUE),ROW(INDIRECT("1:"&ROWS(Mont h)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Copy down until you see blank "Scott Halper" wrote: I have the following data set: Month Item Salesperson Jan B AA Jan B AA Jan B AB Jan B AB Jan B AC Feb B AB Feb B AB Feb B AA I am trying to write a formula that will count the number of multiple sales by a salespersons that have had for Item B. In this example for Jan and Item B it would be two (Salesperson AA & AB both had multiple sales). Thanks for the help. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Duplicate Occurances
Good input, Harlan
Merging our 2 approaches.... How about this non-array formula?: =SUMPRODUCT(--(FREQUENCY(MATCH(A2:A10&B2:B10&C2:C10&"",A2:A10&B2 :B10&C2:C10&"",0),MATCH(A2:A10&B2:B10&C2:C10&"",A2 :A10&B2:B10&C2:C10&"",0))*(A2:A11=E1)*(B2:B11=F1) 1)) *********** Regards, Ron XL2002, WinXP "Harlan Grove" wrote: Ron Coderre wrote... Maybe something like this: With your posted data in A1:C9 Then.... E1: Jan F1: B G1: =SUMPRODUCT(--(FREQUENCY((A2:A10&"_"&B2:B10=E1&"_"&F1) *MATCH(C2:C10&"",C2:C10&"",0),((A2:A10&"_"&B2:B10 <E1&"_"&F1) *ROW(A2:A10))+MATCH(C2:C10&"",C2:C10&"",0))*(A2:A 11&B2:B11<"")1)) .... While I see the point behind your &"_"&, why not just use the direct approach? (A2:A10=E1)*(B2:B10=F1) It's shorter, and I strongly suspect multiple concatenations and one compare take more time than two compares and a multiply. Next, your MATCH(C2:C10&"",C2:C10&"",0) term is a bug in waiting. If there were any other item IDs in the Item column, e.g., if the sample data had been Month Item Salesperson Jan A AA Jan B AA Jan B AB Jan B AB Jan B AC Jan B AC Feb A AA Feb A AB your formula would return 3 rather than 2. Never assume OPs provide realistic sample data. A more robust formula would be =SUM(--(FREQUENCY(IF(($A$2:$A$9=$E$1)*($B$2:$B$9=$F$1), MATCH(IF(($A$2:$A$9=$E$1)*($B$2:$B$9=$F$1),$C$2:$C $9&"",0), $C$2:$C$9&"",0)),ROW($C$2:$C$9)-MIN(ROW($C$2:$C$9))+1)1)) which is an array formula, so need to hold down [Shift] and [Ctrl] keys before pressing the [Enter] key to enter it. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Number of Duplicate Occurances
On Mar 29, 1:57 pm, "Harlan Grove" wrote:
Ron Coderre wrote... Maybe something like this: With your posted data in A1:C9 Then.... E1: Jan F1: B G1: =SUMPRODUCT(--(FREQUENCY((A2:A10&"_"&B2:B10=E1&"_"&F1) *MATCH(C2:C10&"",C2:C10&"",0),((A2:A10&"_"&B2:B10 <E1&"_"&F1) *ROW(A2:A10))+MATCH(C2:C10&"",C2:C10&"",0))*(A2:A 11&B2:B11<"")1)) ... While I see the point behind your &"_"&, why not just use the direct approach? (A2:A10=E1)*(B2:B10=F1) It's shorter, and I strongly suspect multiple concatenations and one compare take more time than two compares and a multiply. Next, your MATCH(C2:C10&"",C2:C10&"",0) term is a bug in waiting. If there were any other item IDs in the Item column, e.g., if the sample data had been Month Item Salesperson Jan A AA Jan B AA Jan B AB Jan B AB Jan B AC Jan B AC Feb A AA Feb A AB your formula would return 3 rather than 2. Never assume OPs provide realistic sample data. A more robust formula would be =SUM(--(FREQUENCY(IF(($A$2:$A$9=$E$1)*($B$2:$B$9=$F$1), MATCH(IF(($A$2:$A$9=$E$1)*($B$2:$B$9=$F$1),$C$2:$C $9&"",0), $C$2:$C$9&"",0)),ROW($C$2:$C$9)-MIN(ROW($C$2:$C$9))+1)1)) which is an array formula, so need to hold down [Shift] and [Ctrl] keys before pressing the [Enter] key to enter it. Thanks Harlan, it worked great. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count number of possible occurances bi-weekly (Can this be done)? | Excel Discussion (Misc queries) | |||
| How can I find duplicate occurances of three cells in rows? | Excel Worksheet Functions | |||
| count number of occurances of a word in a range | Excel Worksheet Functions | |||
| Simple Way to Count the Number of Duplicate Dates on Multiple Worksheets | Excel Discussion (Misc queries) | |||
| count duplicates/total number of occurances | Excel Discussion (Misc queries) | |||
Linear Mode
