Forum Discussion
AverageIF across multiple worksheets
Im trying to Average the same cell across multiple worksheets, but ignore zeros.
My formula is -
=AVERAGEIF('Calculation Tab 1:Calculation Tab 53'!N38,">0")
I Get the return of - #VALUE!
is there a better formula?
Workaround could be with the technique explained here Excel formula: 3D SUMIF for multiple worksheets | Exceljet
Something like
=SUM(Sheet1:Sheet5!A1)/SUMPRODUCT( COUNTIF( INDIRECT("'"&sheets&"'!"&"A1"), ">0"))where sheets is the name of such range
AVERAGEIF does not support 3D referencing.
Here's a list of functions that support 3D referencing (VSTACK, HSTACK, TOCOL, TOROW - also support 3D referencing):
https://support.microsoft.com/en-us/office/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6
It's worth noting if you're an insider with access to VSTACK, you could make this work.
