Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum all sheets if Column L
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum all sheets if Column L
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum all sheets if Column L
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum all sheets if Column L
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 "JBeaucaire" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
All sheets opening at column IV - help! | Excel Discussion (Misc queries) | |||
Repeat column in all sheets | Excel Discussion (Misc queries) | |||
Combining two work sheets using a column in both as a key | Excel Worksheet Functions | |||
One cell from several sheets to one column | Excel Discussion (Misc queries) | |||
How can give the same name to a row or column in different sheets. | Excel Discussion (Misc queries) |