Averaging from multiple worksheets within a workbook

[walkerma]

I'm trying to do some averages for a year to date "cover" sheet. Worksheet 2 is Jan, Worksheet 3 is Feb, etc..through December. Worksheet 1 is the year to date averages. My data is a formula on each worksheet in cell B22, I thought I had the function written correctly however Excel won't take it:

=AVERAGE(IF(January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22,November!B22,December!B22<>0, January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22,November!B22,December!B22,""))

There are blanks,of course, until each month is filled in. Could it be because the number that is in B22 on each worksheet is the result of a formula instead of typed number?? I've spent hours on this...Any ideas out there??

 

[walkerma]

Domenic!!!

HOORAY!!!! YOU ROCK!!! YES!!!!

I put the month names at the bottom of sheet one, then changed the formula to reference those cells and it worked!!!! Oh my gosh, I can't possibly thank you enough!!! I have been beating my keyboard, my head, everything...trying to get this to work for two days!!!

Now I will protect that cell so no one can screw it up!!

Again, I can't thank you enough for being at your keyboard and helping me through this!!!!

 

[Domenic]

The sheet names can be entered in any range of cells you desire, whether in the same worksheet or another worksheet. So if you'd like to list your sheet names in a worksheet called Sheet2, you can. For example, if you want to enter them in B2:B13, enter January in B2, February in B3, March in B4, and so on. Then change the formula to the following...

=AVERAGE(IF(N(INDIRECT("'"&'Sheet2'!$B$2:$B$13&"'!B22"))>0,N(INDIRECT("'"&'Sheet2'!$B$2:$B$13&"'!B22"))))

Don't forget to confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER. Does this help?

Edit: I see we have success, that's great! Glad I could help!

 

[walkerma]

Seriously, you are my HERO!!!

 

[iliace]

PS....I can't take the zeros out of the other sheets because the number appears as a result of a formula so are currently defaulting to zero. To get a blank I'd have to remove my formula and I can't do that

You cannot use the January:December reference within an IF function, so unfortunately this might not be an option for you.