Try this array formula** :
=SUM(IF(FREQUENCY(IF(D2
5="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