average over several columns/spreadsheets

[BrianMH]

I have a workbook with 4 sheets. I am currently getting an average for all the cells in column E on each of these sheets with the current formula. I want an actual average of all the cells that are not 0 and not blank. I don’t want an average of the averages of each sheet if you know what I mean. This formula works but I have a feeling there is a simpler way to do it. Any ideas?


=SUM(SUMIF('Sheet1'!E:E,"<>0"),SUMIF('Sheet2'!E:E,"<>0"),SUMIF('Sheet3'!E:E,"<>0"),SUMIF('Sheet4'!E:E,"<>0"))/SUM(COUNT('Sheet1'!E:E)-COUNTIF('Sheet1'!E:E,0),COUNT('Sheet2'!E:E)-COUNTIF('Sheet2'!E:E,0),COUNT('Sheet3'!E:E)-COUNTIF('Sheet3'!E:E,0),COUNT('Sheet4'!E:E)-COUNTIF('Sheet4'!E:E,0))

[Bob Phillips]

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!E2:E20"),"<>0"))
/SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!E2:E20"),"<>0"))

[JONvdHeyden]

One way:

=SUM(SUMIF(INDIRECT("Sheet" & {1;2;3;4} & "!E:E"),"<>0"))/(SUM(COUNTIF(INDIRECT("Sheet" & {1;2;3;4} & "!E:E"),">0"))+SUM(COUNTIF(INDIRECT("Sheet" & {1;2;3;4} & "!E:E"),"<0")))