Forum Discussion
AVERAGE from several sheets when data is ">0"
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 ...
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:
=--(R2>0)
Fill down to S30.
Now select the sheet where you want to compute the average.
Enter the formula
=SUM(Sheet1:Sheet5!R2:R30)/SUM(Sheet1:Sheet5!S2:S30)
(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.
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.