AVERAGE from several sheets when data is ">0"

New Contributor
Hi, I have many sheets with the same layout and formulas. I want to find the average of all numbers from a specific row, in all the sheets. Problem is, many of the cells contain the number "0", and I want the formula to only choose values that are ">0".
I found that average for only one sheet works with this formula: =AVERAGEIF(SHEET1!R2:R30;">0")

And it works for several sheets without the IF kriteria:

I have tried this:
But this only returns #VALUE, so there must be some error, and I just can't find out what it is.

Can anyone help? I would be so thankful if anyone would have a solution for this!

Best regards
2 Replies


As you have found, AVERAGEIF does not work across multiple sheets; nor do COUNTIF, COUNTIFS, SUMIF etc.


Here is a solution using a helper column:

Select all five sheets by clicking on the sheet tab of Sheet1, then Shift-clicking on the sheet tab of Sheet5.

Insert an empty column in column S.

Enter the following formula in S2:




Fill down to S30.


Now select the sheet where you want to compute the average.

Enter the formula




(You can use another column as helper column if you wish; adjust the final formula accordingly)

Thank you, that's a good idea! And that gave me some more ideas
I ended up extracting all numbers over 0 to the helper column. And then use the AVERAGE formula. Then I can also use MEDIAN an other similar formulas on the column.