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") ))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:

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:G 18")))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Juan" wrote:

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