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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 04:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com