Forum Discussion

Copper Contributor

May 13, 2022

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 the...

excel

Formulas and Functions

SergeiBaklan

Diamond Contributor

May 14, 2022

Scotty485

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