SUMIF MULTIPLE SHEETS

MikeMN

New Member
Joined
Jul 2, 2003
Messages
19
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
 
Hi.

When you go into Name Manager and highlight the refers to: box for your defined name, does the range Excel points to include any blank cells beyond the last listed sheet name?

Perhaps your code to generate the sheet names is designed to enter a null string in certain rows? COUNTA will consider equally null strings in its calculation, and so if these are present at the end of your list of sheet names then the offset range will also include them.

Regards
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi all

A very helpful thread!

I have used the following code: =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetLists&"'!Ac9:Ac2000"),CONCATENATE(F$1,$D2),INDIRECT("'"&SheetLists&"'!r9:r2000"))) which works fine if the "SheetLists" refers only to one or 2 cells which have sheet names in them (i.e. if I use A6:A7). Note that the concatenate is being used to avoid using SUMIFS.

SUMIFS will work equally well as SUMIF...

However the number of worksheets is variable and hence I have set up SheetLists to refer to the following: =OFFSET('Master Data'!$A$6,0,0,COUNTA('Master Data'!$A$6:$A$300)). All this formula is doing is setting the range based on the number of sheetsnames which have been returned (via separate macro) to the Master data sheet. However when I use this offset function, the sumproduct function then shows a #ref! error. I tested the SheetLists offset range via a dropdown validation (to see if my formula had a data entry error in it) and it appears to work ok.

Can anyone assist with this?

Many thanks

D

Make sure that each sheet in SheetList exist in the workbook. Otherwise a #REF! will follow.
 
Upvote 0
Hi,

Many Thanks to Aladin and XOR LX ... !!!

Living encyclopedias ...!!!
 
Upvote 0
I am rather new to excel, some of these formulas go right over my head. I am catching on though, between googling and youtubing.

I have tried both options and have had Zero Success. I am not exactly sure why it is not working.

My current formula I am using is getting rather lengthy. Everytime I add a new page, i add more to the formula.

This is my current Lengthy Formula:

=SUMIF('BLANK PO'!$A$9:$A$41,$B6,'BLANK PO'!$I$9:$I$41)+SUMIF('LC2-0154B'!$A$9:$A$41,$B6,'LC2-0154B'!$I$9:$I$41)+SUMIF('LC3-6151A'!$A$9:$A$41,$B6,'LC3-6151A'!$I$9:$I$41)+SUMIF('LC3-6140A (P1)'!$A$9:$A$41,$B6,'LC3-6140A (P1)'!$I$9:$I$41)+SUMIF('LC3-6160'!$A$9:$A$41,$B6,'LC3-6160'!$I$9:$I$41)+SUMIF('LC3-6160NT'!$A$9:$A$41,$B6,'LC3-6160NT'!$I$9:$I$41)+SUMIF('LC3-6151NT-A'!$A$9:$A$41,$B6,'LC3-6151NT-A'!$I$9:$I$41)+SUMIF('LC3-6160A (P1)'!$A$9:$A$41,$B6,'LC3-6160A (P1)'!$I$9:$I$41)+SUMIF('LC3-6140A (P2)'!$A$9:$A$41,$B6,'LC3-6140A (P2)'!$I$9:$I$41)+SUMIF('LC3-6160A (P2)'!$A$9:$A$41,$B6,'LC3-6160A (P2)'!$I$9:$I$41)+SUMIF('LC3-6151B'!$A$9:$A$41,$B6,'LC3-6151B'!$I$9:$I$41)+SUMIF('LC3-6172'!$A$9:$A$41,$B6,'LC3-6172'!$I$9:$I$41)+SUMIF('LC3-6172NT'!$A$9:$A$41,$B6,'LC3-6172NT'!$I$9:$I$41)

I work in a place, that uses Purchase Orders when we buy something. I have to code everything under each purchase order (each purchase order is another sheet added to the workbook) depending on what we use it for. I am trying to keep track of the total costs for each code. Every sheet with the exception of the Totals page looks the same. So my range areas do not change, just my criterion and sheets.

I do not know what I am doing wrong, I get NAME and REF errors everytime I try to use the option mentioned.

As you can see with my formula, I tell it to look for a specific in ranges $A$9:$A$41, the criterion/code changes but stays put in the $B column, and when it finds a matching code it sums the price, which is found in $I$9:$I$41. I have had no luck so far with spreading it across sheets, unless I add more to the code. Just copy and paste change the sheet listed and add it together... its getting rather lengthy.

Any help would be awesome. Thanks! =)



Two options...

1]

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))

where SheetList refers to a range that lists the relevant sheet names, A2:B10 the relevant range in every sheet in SheetList, and D2 the criterion value.

2] Using the morefunc.xll add-in...

=SUMPRODUCT(--(THREED(Sheet2:Sheet3!$A$2:$A$10)=D2),(THREED(Sheet2:Sheet3!$B$2:$B$10)))

where Sheet2 and Sheet3 are the relevant sheets while A2:B10 is the relevant range and D2 the criterion.
 
Upvote 0
I am rather new to excel, some of these formulas go right over my head. I am catching on though, between googling and youtubing.

I have tried both options and have had Zero Success. I am not exactly sure why it is not working.

My current formula I am using is getting rather lengthy. Everytime I add a new page, i add more to the formula.

This is my current Lengthy Formula:

=SUMIF('BLANK PO'!$A$9:$A$41,$B6,'BLANK PO'!$I$9:$I$41)+SUMIF('LC2-0154B'!$A$9:$A$41,$B6,'LC2-0154B'!$I$9:$I$41)+SUMIF('LC3-6151A'!$A$9:$A$41,$B6,'LC3-6151A'!$I$9:$I$41)+SUMIF('LC3-6140A (P1)'!$A$9:$A$41,$B6,'LC3-6140A (P1)'!$I$9:$I$41)+SUMIF('LC3-6160'!$A$9:$A$41,$B6,'LC3-6160'!$I$9:$I$41)+SUMIF('LC3-6160NT'!$A$9:$A$41,$B6,'LC3-6160NT'!$I$9:$I$41)+SUMIF('LC3-6151NT-A'!$A$9:$A$41,$B6,'LC3-6151NT-A'!$I$9:$I$41)+SUMIF('LC3-6160A (P1)'!$A$9:$A$41,$B6,'LC3-6160A (P1)'!$I$9:$I$41)+SUMIF('LC3-6140A (P2)'!$A$9:$A$41,$B6,'LC3-6140A (P2)'!$I$9:$I$41)+SUMIF('LC3-6160A (P2)'!$A$9:$A$41,$B6,'LC3-6160A (P2)'!$I$9:$I$41)+SUMIF('LC3-6151B'!$A$9:$A$41,$B6,'LC3-6151B'!$I$9:$I$41)+SUMIF('LC3-6172'!$A$9:$A$41,$B6,'LC3-6172'!$I$9:$I$41)+SUMIF('LC3-6172NT'!$A$9:$A$41,$B6,'LC3-6172NT'!$I$9:$I$41)

I work in a place, that uses Purchase Orders when we buy something. I have to code everything under each purchase order (each purchase order is another sheet added to the workbook) depending on what we use it for. I am trying to keep track of the total costs for each code. Every sheet with the exception of the Totals page looks the same. So my range areas do not change, just my criterion and sheets.

I do not know what I am doing wrong, I get NAME and REF errors everytime I try to use the option mentioned.

As you can see with my formula, I tell it to look for a specific in ranges $A$9:$A$41, the criterion/code changes but stays put in the $B column, and when it finds a matching code it sums the price, which is found in $I$9:$I$41. I have had no luck so far with spreading it across sheets, unless I add more to the code. Just copy and paste change the sheet listed and add it together... its getting rather lengthy.

Any help would be awesome. Thanks! =)

Create a range with the following sheet names:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][/tr]
[tr][td]
2​
[/td][td]BLANK PO[/td][/tr]

[tr][td]
3​
[/td][td]LC2-0154B[/td][/tr]

[tr][td]
4​
[/td][td]LC3-6151A[/td][/tr]

[tr][td]
5​
[/td][td]LC3-6140A (P1)[/td][/tr]

[tr][td]
6​
[/td][td]LC3-6160[/td][/tr]

[tr][td]
7​
[/td][td]LC3-6160NT[/td][/tr]

[tr][td]
8​
[/td][td]LC3-6151NT-A[/td][/tr]

[tr][td]
9​
[/td][td]LC3-6160A (P1)[/td][/tr]

[tr][td]
10​
[/td][td]LC3-6140A (P2)[/td][/tr]

[tr][td]
11​
[/td][td]LC3-6160A (P2)[/td][/tr]

[tr][td]
12​
[/td][td]LC3-6151B[/td][/tr]

[tr][td]
13​
[/td][td]LC3-6172[/td][/tr]

[tr][td]
14​
[/td][td]LC3-6172NT[/td][/tr]
[/table]


Select the above range and name the selection SheetList.

Now invoke:

Either...

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

Or...

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A9:A41"),$B6,INDIRECT("'"&SheetList&"'!I9:I41"))
 
Upvote 0
Create a range with the following sheet names:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]BLANK PO[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]LC2-0154B[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]LC3-6151A[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]LC3-6140A (P1)[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]LC3-6160[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]LC3-6160NT[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]LC3-6151NT-A[/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]LC3-6160A (P1)[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]LC3-6140A (P2)[/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]LC3-6160A (P2)[/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]LC3-6151B[/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]LC3-6172[/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]LC3-6172NT[/TD]
[/TR]
</tbody>[/TABLE]


Select the above range and name the selection SheetList.

Now invoke:

Either...

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

Or...

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


Still getting a REF! error. Though this time I understand the "SheetList"
Excel.jpg
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top