Sum all sheets if Column L

[Juan]

Hi,
Is it possible to sum all sheets in a workbook within a range base on Column L
I currently use the following formula which works if I have one sheet
B5 =SUMIF(Week5!L9:L18,"Won",Week5!G9:G18)
B6 =SUMIF(Week5!L9:L18,"Lost",Week5!G9:G1
B12 =SUMIF(Week5!L24:L33,"Won",Week5!G24:G33)
B13 =SUMIF(Week5!L24:L33,"Lost",Week5!G24:G33)
My workbook as week5 upto week 10 and every week I'll be adding a week,
so I want to automate so that I don't have to manually add totals.
Is this doable?
Really appreciate any help I can get.
Thanks
Juan

 

[JBeaucaire]

Try this instead, it's easy to expand too:

=SUM(SUMIF(INDIRECT("Week"&{5,6,7,8,9,10}&"!L9:L18"),"="&"Won",INDIRECT("Week"&{5,6,7,8,9,10}&"!G9:G18")))

 

[JBeaucaire]

Slight correction:

=SUM(SUMIF(INDIRECT("Week"&{5,6,7,8,9}&"!L9:L18"),"Won",INDIRECT("Week"&{5,6,7,8,9}&"!G9:G18")))

Or, if the word "won" was in cell A1, then:

=SUM(SUMIF(INDIRECT("Week"&{5,6,7,8,9}&"!L9:L18"),"="&A1,INDIRECT("Week"&{5,6,7,8,9}&"!G9:G18")))

If you go ahead and create all the sheets, even the ones not needed yet and
have no data, the formula can be constructed with all the eventual sheet
numbers and it will accrue as the data is added:

=SUM(SUMIF(INDIRECT("Week"&{5,6,7,8,9,10}&"!L9:L18"),"Won",INDIRECT("Week"&{5,6,7,8,9,10}&"!G9:G18")))

 

[Juan]

Hello
Mr. Rocket scientis lol, Thanks a lot this seems to work fine. Just one
question, so every time I add a new sheet, I will need to add that to the
formula manually right? becuase I tested and if I don't have that sheet in my
formula I get an error. This is not a problem.
Once again thanks alot JB
Have a good afternoon

Juan