I found a formula that works even smoother - I'm not sure why, but here it is:

=IF(SUMIF($B$8:$B$4140,A5,$L$8:$L$4140)=0,SUMIF($B$8:$B$4140,A5,$Z$8:$Z$4140),SUMIF($B$8:$B$4140,A5,$L$8:$L$4140))

In this formula, A is the reference name, B is the name in the worksheet, L
is the Monday column, and Z is the Average column. Thanks so much for your
help!

"Bob Phillips" wrote:

> If ID is in B, Mon In C, Avg in J, then try
>
> =IF(B2:B10="a",SUM(IF(C2:C10<>"",C2:C10,J2:J10)))
>
> which is an array formula, so commit with Ctrl-Shift-Enter
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>