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

 

[XOR LX]

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

 

[Aladin Akyurek]

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.

 

[dmcgoo]

Thanks guys, I found the problem based on your comments above!

 

[James006]

Hi,

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

Living encyclopedias ...!!!

 

[JHopp82]

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.

 

[Aladin Akyurek]

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:

Row\Col	A​
2​	BLANK PO
3​	LC2-0154B
4​	LC3-6151A
5​	LC3-6140A (P1)
6​	LC3-6160
7​	LC3-6160NT
8​	LC3-6151NT-A
9​	LC3-6160A (P1)
10​	LC3-6140A (P2)
11​	LC3-6160A (P2)
12​	LC3-6151B
13​	LC3-6172
14​	LC3-6172NT

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

 

[JHopp82]

Create a range with the following sheet names:

Row\Col	A​
2​	BLANK PO
3​	LC2-0154B
4​	LC3-6151A
5​	LC3-6140A (P1)
6​	LC3-6160
7​	LC3-6160NT
8​	LC3-6151NT-A
9​	LC3-6160A (P1)
10​	LC3-6140A (P2)
11​	LC3-6160A (P2)
12​	LC3-6151B
13​	LC3-6172
14​	LC3-6172NT

<tbody>
</tbody>

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"

 

[Aladin Akyurek]

Still getting a REF! error. Though this time I understand the "SheetList" [...]

Does each sheet exist, listed in SheetList?

 

[JHopp82]

Does each sheet exist, listed in SheetList?

Yup. All the sheets exist. I am going to see about changing the names of them, simplify them a little more, maybe that will help.

 

[Aladin Akyurek]

Yup. All the sheets exist. I am going to see about changing the names of them, simplify them a little more, maybe that will help.

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