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.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]JAD[/td][td]

56​

[/td][/tr]

[tr][td]

7​

[/td][td]NAD[/td][td]

4​

[/td][/tr]

[tr][td]

8​

[/td][td]XAD[/td][td]

22​

[/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][/tr]
[/table]

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]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]JAD[/TD]
[TD]

56​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]NAD[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]XAD[/TD]
[TD]

22​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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"))