sumif across multiple sheets

[agnesz]

I'm working on a sumproduct/sumif formula that will sumif across multiple sheets in document named Locsum Report.xls...

the first sheet in the workbook will always be named 1, and the last sheet will always be named 10...

I have this so far:

=SUMPRODUCT(SUMIF(INDIRECT('[LocSum Report.xls]&ROW(INDIRECT("1:10"))&"!$c$14:$c$1000"),$A$3,INDIRECT('[LocSum Report.xls]&ROW(INDIRECT("1:10"))&"!$m$14:$m$1000")))

where $a$3 = "metro nyc region", column c is a list of region names, column m represents the sales that I only want to pull for metro nyc region

Any help would be greatly appreciated.
Thank you

[Bob Phillips]

=SUMPRODUCT(SUMIF(INDIRECT("'[LocSum Report.xls]"&ROW(INDIRECT("1:10"))&"'!$c$14:$c$1000"),$A$3,
INDIRECT("'[LocSum Report.xls]"&ROW(INDIRECT("1:10"))&"'!$m$14:$m$1000")))

[agnesz]

Unfortunately I'm still getting a #REF! error... I attached the report itself, maybe it'll be easier to troubleshoot that way...

[Bob Phillips]

You didn't.

[agnesz]

Sorry I attached to my original post...
az

[Shazam]

See the attachment below.

[Bob Phillips]

If you want Sheet1 and 2 then use

=SUMPRODUCT(SUMIF(INDIRECT("'[LocSum Report.xls]Sheet"&ROW(INDIRECT("1:2"))&"'!$c$14:$c$1000"),$A$3,
INDIRECT("'[LocSum Report.xls]Sheet"&ROW(INDIRECT("1:2"))&"'!$m$14:$m$1000")))

adjust the 1:1 to the actual number of sheets.

[agnesz]

The issue is that i actually want to sum all tabs in between 1 and 2, this time there happen to be two tabs, but next time there may be more than that with different sheet names.

[Bob Phillips]

You have to know how many there are, there is no way (that I know of) without defining the names of all sheets tgo be summed. You can be cute in how you get those names, but you have to know.

[Shazam]

The issue is that i actually want to sum all tabs in between 1 and 2, this time there happen to be two tabs, but next time there may be more than that with different sheet names.


If you can post the expected results along with the secnario problem you're saying. Then I could see if I could come up with something.

[agnesz]

Shazam -

What you've given me is perfect, except I need the formula to work from another workbook... I'm attaching said document, which is named "RMM MONDAY" which needs to link up to LocSum Report.xls. I highlighted the cells in question in yellow.

I've input the tab names on the top sheet (RMM MONDAY), now I just can't seem to figure out how to adjust the formula so that it goes out to LocSum Report and then goes to the designated sheets and sums them up...

Your help is so very much appreciated...
Thank you again!
az