Count Number of Duplicate Occurances

[Scott Halper]

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.

 

[Peo Sjoblom]

=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

 

[Guest]

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: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]

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&"_"&F1)*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 endof
the data.

Does that help?
***********
Regards,
Ron

XL2002, WinXP








- 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.

 

[Harlan Grove]

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:A11&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.

 

[Guest]

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")*(Helper=TRUE),ROW(INDIRECT("1:"&ROWS(Month)))),ROWS($1:1))),"",INDEX(Salesperson,SMALL(IF((Month="Jan")*(Item="B")*(Helper=TRUE),ROW(INDIRECT("1:"&ROWS(Month)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
Copy down until you see blank

 

[Guest]

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

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:A11&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.

 

[Scott Halper]

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:A11&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.