SUMIF MULTIPLE SHEETS

[MikeMN]

I have used the sumif formula many times ofer the years.

However, I have always only refrences one sheet.

I am now tying to refrence several sheets.

But I am getting a #value error message.

Any thoughts if this can be done.

Thank you

Mike

 

[JHopp82]

Yes that might help for it's hard to cope with complicated names in an error-free manner.

So I just tried renaming it to just the numbers took off the "LC3-" and no luck.

 

[Aladin Akyurek]

So I just tried renaming it to just the numbers took off the "LC3-" and no luck.

Row\Col	B​	C​
5​
6​	JAD	56​
7​	NAD	4​
8​	XAD	22​
9​

In C6 of summary enter and copy down:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!I9:I41"),
   INDIRECT("'"&SheetList&"'!A9:A41"),$B6))

See the link for a sample implementation: https://dl.dropboxusercontent.com/u/65698317/JHopp82 SUMIF(S) MULTIPLE SHEETS.xlsx

 

[JHopp82]

Row\Col	B​	C​
5​
6​	JAD	56​
7​	NAD	4​
8​	XAD	22​
9​

<tbody>
</tbody>

In C6 of summary enter and copy
=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!I9:I41"),
INDIRECT("'"&SheetList&"'!A9:A41"),$B6))
[/code]

See the link for a sample implementation: https://dl.dropboxusercontent.com/u/65698317/JHopp82 SUMIF(S) MULTIPLE SHEETS.xlsx

I am pretty certain I am doing this the same way you are. But still no results, Just a #REF! error everytime. Do you have an email where I can email you the file and you can look it over, maybe?

Your example seems to be set up just the way I am setting mine up (for the most part).

 

[Aladin Akyurek]

I am pretty certain I am doing this the same way you are. But still no results, Just a #REF! error everytime. Do you have an email where I can email you the file and you can look it over, maybe?

Your example seems to be set up just the way I am setting mine up (for the most part).

SheetList must contain sheets which all must exist, otherwise a #REF! error will follow.

 

[JHopp82]

SheetList must contain sheets which all must exist, otherwise a #REF! error will follow.

I used a macro that lets me drag a code down and lists the name of all my sheets. I then selected the boxes that listed my sheets and named it "SheetList". I have done this both with the macro and without the macro because I thought the macro/code might interfere. But still got the #REF! error. I am off work atm. Will play around with it some more tomorrow if I have the time.

I do appreciate the patience and help you are giving me. I might just try rebuilding my entire WorkBook and see if that helps. I do have other Vendors I have yet to make a WorkBook for.

I always kept putting Excel off and using Word all the time b/c it looked difficult to learn.. lol.. I got a little older (32) and now I am enjoying the challenge of making my paperwork simpler... lol

Anyways. Thanks again for the help. If I get a chance tomorrow I am going to make another workbook and see if I can get it to work from there. Thanks!

 

[Aladin Akyurek]

I used a macro that lets me drag a code down and lists the name of all my sheets. I then selected the boxes that listed my sheets and named it "SheetList". I have done this both with the macro and without the macro because I thought the macro/code might interfere. But still got the #REF! error. [...]

Could you post the formula you have?

 

[redpipe67]

Hi Aladin,
This thread is amazing and your generous assistance is commendable. I would like to thank you for solving a question I had when I found this with a google search. I do have a question also.
From your first response I used option 1 and with a few changes to reflect my own situation, it works like a charm.
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B19:B31"),B3,INDIRECT("'"&SheetList&"'!M19:M31")))
B19:B31 is a list of names and M19:M31 is a value on each of the sheets in my workbook and the desired total arrives depending upon the name in B3 in the above. Thanks.
I would like to know how to introduce one more factor as in range INDIRECT("'"&SheetList&"'!G19:G31"),D2,
D2 being the word "On".
So in all, if the name in B3 also has the value "On" in column G then the value in column M would total, not just from the name in B3.
One small problem though, this must be a formula than Excel 2003 can also understand as this version is still currently used by our group.

Thank you for your time.

 

[Aladin Akyurek]

@redpipe67

Is this 2003 version on a 32bit system?

 

[redpipe67]

Thank you for replying Aldin,
We have a variety of operating systems being used, some of them XP although these are in the process of being replaced with Windows 7 64 bit so I would say possibly yes right now but no within a short period.

 

[Aladin Akyurek]

Thank you for replying Aldin,
We have a variety of operating systems being used, some of them XP although these are in the process of being replaced with Windows 7 64 bit so I would say possibly yes right now but no within a short period.

Windows 7 32bit and earlier OS versions run Excel 2003 and allow for running Longre's morefunc.xll add-in. If you install this add-in, you can use Longre's THREED. This would give you:

=SUMPRODUCT(
    THREED(Sheet2:Sheet3!$M$19:$M$31),
    --(THREED(Sheet2:Sheet3!$B$19:$B$31)=B3),
    --(THREED(Sheet2:Sheet3!$G$19:$G$31)="On"))

Excel itself should have this THREED!...

When you are on a more recent Excel version (with SUMIFS), you can have:

=SUMPRODUCT(
   SUMIFS(INDIRECT("'"&SheetList&"'!M19:M31"),
     INDIRECT("'"&SheetList&"'!B19:B31"),B3,INDIRECT("'"&SheetList&"'!G19:G31"),"On"))