Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct but only first time corresponding value is seen e.g. un
Using XL03
Sample Data: Name Race Speed Avg Speed Track Month Joe 2:12 2:15 OFCF Jan Joe 2:28 2:15 ADL Feb Mary 2:14 2:07 ADL Jan Joe 2:15 2:15 ADL Feb etc I already have sumproducts in place to count how many participants raced in each month, how many at each track, etc. The last piece I need to add is the number of people for a given track on a given month, who had an /average/ time of over 2 minutes. But, I have repeats- the same person can run a track more than once in the same month, so their average comes across more than once and skews the total count. Is there a straightforward way to only count the "unique" value combinations (first time for combo of person, track, month) and ignore all subsequent races by that person on the same track/month in the sumproduct count? Thank you! Keith |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct but only first time corresponding value is seen e.g. un
Try this array formula** :
=SUM(IF(FREQUENCY(IF(D2:D5="ADL",IF(E2:E5="Feb",IF (C2:C5TIME(0,2,0),MATCH(A2:A5,A2:A5,0)))),ROW(A2: A5)-ROW(A2)+1),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "ker_01" wrote in message ... Using XL03 Sample Data: Name Race Speed Avg Speed Track Month Joe 2:12 2:15 OFCF Jan Joe 2:28 2:15 ADL Feb Mary 2:14 2:07 ADL Jan Joe 2:15 2:15 ADL Feb etc I already have sumproducts in place to count how many participants raced in each month, how many at each track, etc. The last piece I need to add is the number of people for a given track on a given month, who had an /average/ time of over 2 minutes. But, I have repeats- the same person can run a track more than once in the same month, so their average comes across more than once and skews the total count. Is there a straightforward way to only count the "unique" value combinations (first time for combo of person, track, month) and ignore all subsequent races by that person on the same track/month in the sumproduct count? Thank you! Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct & nagetive time | New Users to Excel | |||
Time Sheet - Hlookup, Index, SumProduct or what? | Excel Discussion (Misc queries) | |||
Sumproduct with date and time? | Excel Discussion (Misc queries) | |||
Sumproduct with date and time? | Excel Discussion (Misc queries) | |||
SUMProduct working most of the time... | Excel Discussion (Misc queries) |