 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Sumif across mutiple sheets
I almost found something here, but not exactly what I need. I have 20 sheets named (tabbed) after different cities (let's say starts with "SHA", ends with "SYD"). Their row 10 (from column F to Z) contains when the sales happens (2008,2009 etc, BUT each has a different start). Row 12 (from column F to Z) has the corresponding sales amount. I also have a total sheet and in the total sheet I need to sum up the sales of all the cities by year (from column F=2008 to column Z=2028). I find a fomula here: =SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!A:A"),"EU",INDIRECT("Sheet"&{1,2,3}&"!C:C"))). The only problem is I have 20 sheets and I tried to use "SHA:SYD!F10:Z10" to replace "Sheet"&{1,2,3}&"!C:C" but it doesn't work. Any idea? Thanks! Put the sheet names in M1:M20 and use =SUMPRODUCT(SUMIF(INDIRECT(M1:M20&"!A:A"),"EU",INDIRECT(M1:M20&"!C:C")))  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I almost found something here, but not exactly what I need. > [quoted text clipped - 13 lines] > to > replace "Sheet"&{1,2,3}&"!C:C" but it doesn't work. Any idea? Thanks! Thanks a lot! Just for my curiosity, why sumproduct? I tried sum, it didn't work. I didn't think of using sumproduct at all. > Put the sheet names in M1:M20 and use > [quoted text clipped - 17 lines] > > to > > replace "Sheet"&{1,2,3}&"!C:C" but it doesn't work. Any idea? Thanks! SUM should work if array-entered, but my approach was different to yours. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Thanks a lot! Just for my curiosity, why sumproduct? I tried sum, it > didn't [quoted text clipped - 25 lines] >> > to >> > replace "Sheet"&{1,2,3}&"!C:C" but it doesn't work. Any idea? Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.