AVERAGEIF function on different cells in different sheets-CodePudding

I'm currently working on a project which requires me to average 4 non-zero values from 4 different worksheets. I've tried these functions but it returns an #ERROR!.

=AVERAGEIF([Monday!I2,Tuesday!I2,Wednesday!I2,Thursday!I2],"<>0")

=AVERAGEIF((Monday!I2,Tuesday!I2,Wednesday!I2,Thursday!I2),"<>0")

=SUMIF((Monday!I2,Tuesday!I2,Wednesday!I2,Thursday!I2),"<>0")/COUNTIF((Monday!I2,Tuesday!I2,Wednesday!I2,Thursday!I2),"<>0")

I think there is a problem with the range that is messed up because Excel doesn't recognize it. Is there a way to average these numbers?

CodePudding user response：

Use INDIRECT with SUMPRODUCT:

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Monday","Tuesday","Wednesday","Thursday"}&"'!I2"),"<>0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Monday","Tuesday","Wednesday","Thursday"}&"'!I2"),"<>0"))

CodePudding user response：

If the entries in those cells are strictly non-negative, I would prefer a non-volatile set-up which also does not require the creation of an array of worksheet names.

=SUM(Monday:Thursday!I2)/INDEX(FREQUENCY(Monday:Thursday!I2,0),2)

which assumes that Monday and Thursday are the leftmost and rightmost of the sheets to be interrogated.

Edit: If those cells can contain negative numbers, it may still be worth trying:

=SUM(Monday:Thursday!I2)/SUM(INDEX(FREQUENCY(Monday:Thursday!I2,{-1,0}),{1,3}))

where, in this case, it is assumed that none of the entries are >-1 and <0.