AVERAGE from several sheets when data is ">0"

Copper 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:
=AVERAGE(SHEET1:SHEET5!R2:R30)

I have tried this:
=AVERAGEIF(SHEET1:SHEET5!R2:R30;">0")
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
Sissel
2 Replies

@SisselLarsen

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:smiling_face_with_smiling_eyes:
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.