That doesn't work for me.

SUMPRODUCT(--(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4,INDIRECT("' "&WSL&"'!D4:D30"))0))

Returns the incorrect count

Biff

"Harlan Grove" wrote in message
oups.com...
quailhunter wrote...
I'm trying to find the average for a person with scores in column 4 on 3
separate sheets. The following function returns #VALUE.. What am I doing
wrong? Because the person may be in a different row on each sheet, I'm
thinking I need to use the VLOOKUP function.... Help!!! Also, as an
aside, the person may have a zero on one sheet that would affect the
average score...

=AVERAGE(VLOOKUP(A4,Week03:Week01!A$4:G$30,4,0 ))

If you're using VLOOKUP, there'd be at most only one match for A4 in
any of the worksheets' A4:A30 ranges? If so, you'd need a list of the
worksheet names in some range (I'll refer to it as WSL), then you could
try

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4, INDIRECT("'"&WSL&"'!D4:D30")))
/SUMPRODUCT(--(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4,INDIRECT("' "&WSL&"'!D4:D30"))0))