Try the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(N(INDIRECT("'"&D1:D5&"'!G51"))0,N(IND IRECT("'"&D1:D5&"'!G51"
))))

....where D1:D5 contain your sheet names. Adjust this accordingly.

Hope this helps!

In article ,
"Todd" wrote:

Hi,

I am adding accross sheets in a workbook with SUM('Start!:End!'!G51) and
need a way to calculate the average of the result. The number of sheets
change and not always will the cells have data. Sometimes they will be blank
and sometimes they contain zero's. I can't figure out how to count how many
of the sheets have numbers larger than zero to divide with.

something like
SUM('Start!:End!'!G51)/(COUNTA('Start!:End!'!g51)

except able to know how many cells are blank or contain zero's.

Any ideas?


Todd