Hi Harlan,

Yeah, I figured that myself afterwards. Worked at start and with OPs data,
but fell apart when I tried to make it generic to save in the library - by
which time, Domenic had posted.

Bob


"Harlan Grove" wrote in message
oups.com...
Bob Phillips wrote...
How about

=SUMPRODUCT(((B2:B20=16)*(C2:C20="BRY")*(A2:A20< ""))
/COUNTIF(A2:A20,A2:A20&""))
...

Doesn't work. OP's sample data was too easy - no variation in criteria
fields. Using the OP's sample data, and adjusting your formula to refer
to rows 2 to 12, change the first Week# field for EE ID 100015 to 22.
Your formula then returns 6.667, which is a clear error (counts are
always integers).

The problem is that you can't use COUNTIF in the denominator because
it'll count *ALL* instances of each value in A2:A12.

You gotta get tricky, and the OP was closer than he (or you) thought.

=COUNT(1/FREQUENCY((B2:B12=16)*(C2:C12="BRY")*MATCH(A2:A12, A2:A12,0),
ROW(INDIRECT("1:"&ROWS(B2:B12)))-1))
-(SUMPRODUCT((A1:A12=A15)*(C1:C12=B15)-1)0)

The trick is putting the criteria *inside* the FREQUENCY call.
Subtracting 1 from ROW()'s result creates a 0 bin for the records that
don't satisfy the criteria. The final SUMPRODUCT term excludes the 0
bin if there are any records that don't satisfy the criteria. Also note
that this doesn't have to be entered as an array formula.