| Home |
| Search |
| Today's Posts |
|
#7
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. |
| 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) | |||
Threaded Mode
