Average across worksheets ignoring zero
One way by specifying the sheet names..
=SUMPRODUCT(SUMIF(INDIRECT("'"&{"sheet1","sheet2"} &"'!F37"),"<0"))/
SUMPRODUCT(COUNTIF(INDIRECT("'"&{"sheet1","sheet2" }&"'!F37"),"<0"))
'OR having the sheet names in J1:J3 (no blank cells)
=SUMPRODUCT(SUMIF(INDIRECT("'"& J1:J3 &"'!F37"),"<0"))/
SUMPRODUCT(COUNTIF(INDIRECT("'"& J1:J3 &"'!F37"),"<0"))
If this post helps click Yes
---------------
Jacob Skaria
"Ted" wrote:
I am using =AVERAGE(Start:End!F37) to calculate an average across multiple
worksheets. However, some of the sheets contain a zero in cell B37. I need
to ignore that zero (and that sheet) entirely. Can anyone help?
|