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.

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.

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.

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.

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.

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.

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.

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.