Prev: Conditional Format for Blank Cells
Next: Count with two criteria
From: ker_01 on 18 Mar 2010 12:35
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
From: T. Valko on 18 Mar 2010 13:20
Try this array formula** :

=SUM(IF(FREQUENCY(IF(D2:D5="ADL",IF(E2:E5="Feb",IF(C2:C5>TIME(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" <ker01(a)discussions.microsoft.com> wrote in message
news:702D94CF-902D-4E78-B1C1-840B24E827A4(a)microsoft.com...
> 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


 | 
Pages: 1
Prev: Conditional Format for Blank Cells
Next: Count with two criteria