Average in Multiple Sheets

[Paiwand]

Hello Dear.

I have an workbook which is consist of 13 sheets, starts form (Jan,Feb,Mar,...Dec) plus (Annual), at the Annual Sheet i would like to getting an cell's average which is exist in all sheets and its (D17) the average should exclude and unconsidered zero and blank. i have used the below formula but it doesn't work.

=AVERAGEIFS(Jan!D17;Feb!D17;Mar!D17;Apr!D17;May!D17;Jun!D17;Jul!D17;Aug!D17;Sep!D17;Oct!D17;Nov!D17;Dec!D17;"0%")

Please your kind help.

Best regards.

 

[Aladin Akyurek]

Although I generally know how to avoid such errors, unfortunately in this case, I can't do that as the sheet is password protected.

I see. We have to switch to a different method.

Create a range which houses the names of the relevant sheets. Select this range and name the selection SheetList.

Now invoke:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!C10:C33"),INDIRECT("'"&SheetList&"'!C10:C33"),{"<0",">0"}))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!C10:C33"),{"<0",">0"}))

 

[dwgnome]

Thank you Aladin, I'll give it a try.
Problem I think I will have and that is why I used the First/Last approach is that if a worksheet does not exist in that SheetList, then I will get an error. I am using a software system that writes from a template that adds a new worksheet each day and records hourly data on that worksheet. The tab names are 01 thorough 31.

 

[Aladin Akyurek]

Thank you Aladin, I'll give it a try.
Problem I think I will have and that is why I used the First/Last approach is that if a worksheet does not exist in that SheetList, then I will get an error. I am using a software system that writes from a template that adds a new worksheet each day and records hourly data on that worksheet. The tab names are 01 thorough 31.

See if you can manage to use the technique described in post #22 of:

https://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets-2.html

which allows you to dynamically construct the SheetList.

 

[dwgnome]

Okay, I'll check it out and let you know if I can reproduce the dynamic sheetlist.

 

[killz]

But how do you remove the "0" ?

 

[killz]

Is dynamic range the reason why the formula is so?