Dividing a number by the number of sheets where a cell is more than 0

[hsl9999]

I have a workbook with 101 sheets. Field A1 on Sheet1 through A1 on sheet100 may or may not contain a number greater than 0. On sheet101 I have the sum of all numbers from A1 of Sheet1 through A1 of Sheet100. What can I put as a formula if I want to take that total and divide by the number of sheets where A1 is not 0. For example there are 100 sheets between Sheet1 and Sheet100 but only 47 contain numbers greater than 0. So I want to get the average by dividing the sum of all values, between Sheet1 and Sheet100, divided by 47. Thank you.

 

[DRSteele]

I'm working on it. It's troubling to discover that AVERAGEIFS, COUNTIFS, SUMIFS don't work across sheets.

 

[jim may]

MAYBE...

=COUNT(Sheet2:Sheet11!A1)

<colgroup><col> </colgroup><tbody>
</tbody>

 

[DRSteele]

I think I came up with something that works.

This formula should report the number of non-0 in A1 of the worksheets named specifically Sheet1 to Sheet100 and those sheets between them. Pay particular attention to the syntax of the formula where the word Sheet is used (inside INDIRECT) and the values 1, 100, and 0.

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:"&100))&"'!A1"),"<>"&0))

 

[hsl9999]

Did not work. Posibly a mistype?

 

[hsl9999]

I tried this carefully copying and applying but it did not work for me. Possibly a typo?

 

[DRSteele]

I can't know if you made a type-o.

All your sheets must be named in this fashion: Sheet1 (with no spaces, all the way up to 100); there must be exactly 100 sheets, and Sheet1 must come before Sheet100 in the worksheet tabs list. Try it on smaller sample set, say with five sheets, some of which have a 0 in the relevant cell.

 

[Dryver14]

DRSteele, just tried this and it works but fails if one of the sheets is not there.

HSL9999 is everysheet there 1 to 100?

I see you have noticed this...never mind

 

[DRSteele]

Yes, the portion ROW(INDIRECT("1:"&100)) reports an array of numerals 1 to 100. The next portion inside INDIRECT creates a huge array of references that are all of the form "'Sheet80'!A1" , for example. Those references are all passed into the array of COUNTIFS, which evaluates the conditional count.