Averageif of cells from three sheets

[KMK_79]

I have a workbook with four sheets.

All sheets have the same row:column format and relative references, as below

The three sheets have unique names and raw data reports. This fourth sheet is trying to do an average of all three data reports (i.e. cell B2 from all three sheets), provided they are non-zero and non-blank).

I've spent a few hours searching how to achieve AVERAGEIF conditional lookups and the only relevant one I found was related to sumproduct of doing sumifs/countifs. See formula written below:

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!B2"),"<>0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!B2"),"<>0"))

The problem is when I copy this formula past cells B2 to B3 and C2 across the relative references do not change. Please help!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[KMK_79]

Office 365 for Enterprise - company laptop

 

[Fluff]

Thanks for that.
if your 3 sheets are consecutive, you can use

=LET(v,VSTACK(Sheet1:Sheet3!B2),AVERAGE(FILTER(v,v<>0)))

 

[KMK_79]

Interesting - did not yield any result but gave me a #CALC! error

What does that relate to?

 

[Fluff]

That suggests that none of the B2 cells have a value other than blank or 0

 

[KMK_79]

THANK YOU @Fluff

I dragged the formula across the tables and IT WORKED!

Thank you so much - really appreciate it.

Now for the rationale - I can't make out LET and VSTACK functions - can you direct me to appropriate tools to read up how to structure such formulas please?

 

[Fluff]

Have a look here Exceljet they show how most functions work.