Count Number of Duplicate Occurances

S

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

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
 
R

Ron Coderre

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
 
S

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 -
Click to expand...

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

Harlan Grove

Ron Coderre said:
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))
Click to expand...
....

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

Teethless mama

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
 
R

Ron Coderre

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 said:
Ron Coderre said:
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))
Click to expand...
....

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.
Click to expand...
 
S

Scott Halper

Ron Coderre said:
Maybe something like this:
Click to expand...
With your posted data in A1:C9
Click to expand...
Then....
E1: Jan
F1: B
G1:
Click to expand...
=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))
Click to expand...

...

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.
Click to expand...

Thanks Harlan, it worked great.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3
Sorting Revision Labels in Excel 1
Linking to other worksheets 3
Formula needed to generate report! 4
CALCULATE DAYS PER MONTH BETWEEN 2 DATES 3
Counting number of cells until a criteria is met 0
Named Range that uses "relative" range - Possible? 5
OR in SUMPRODUCT formula 4

Top