Ranging Worksheets in an IF statement

[LucasR]

So basically i have 12 sheets and i need to sum the same cell on each sheet and total it on a summary sheet. but i only need to sum that cell IF there is a preset code entered.
the formula i am using to do this seems excessively long right now here is an example for one one cell is :" =IF(JANUARY!Q9=1100,SUM(JANUARY!R9),"0") " i have 10 cells that have 10 potential codes that need to be added and i have 5 time cards per sheet. as you can imagine this ends up being a very large formula. what i need to do is some how range this so i can cut down on space. i tried : " =IF(JANUARY:DECEMBER!Q9=1100,SUM(JANUARY:DECEMBER!R9),"0") " but this wont work. and i have not been able to find anything that does. i am curious if i can even have this done as a range because the formula above looks like it would theoretically read " if 1100 is seen anywhere between Jan - dece in Q9 then sum all the R9 cells in between " so that wouldn't work either.

Any comments and ideas for how to do what i want might help

thanks in advance!

 

[Eric W]

Welcome to the board.

Excel's 3-D formulas are pretty limited. I'd love to see a 3-D SUMIF, but no such luck so far. This leaves us with some exotic array formulas we can try. For example, put the names of your sheets (JANUARY, FEBRUARY, etc.) in AA1:AA12 on your summary sheet. Then use this formula:

=SUM(IF(SUBTOTAL(9,INDIRECT(AA1:AA12&"!Q9"))=1100,SUBTOTAL(9,INDIRECT(AA1:AA12&"!R9"))))

and confirm it in the formula bar by pressing Control+Shift+Enter.

 

[LucasR]

Thanks!

Well i dont think i would have come up with that haha! thanks so much it works perfectly!

thanks a bunch!!

 

[Eric W]

You're welcome!