Feb 05 2021 03:27 AM
Feb 05 2021 07:27 AM
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)
Feb 07 2021 12:06 AM