# Compound if statement?

#### graphpak

##### New Member
Here is my situation:

I am working with 3 worksheets...each worksheet represents a location of a warehouse. There are 2 columns in each worksheet, one an item# and the next a quantity. What I'm trying to do is create a formula or possibly a macro that would allow me to enter the item# and excel would lookup every instance of that item# at all 3 locations and then calculate a total number of pieces from the 3 locations. Can someone please help me with this formula! THANKS!!

I am having trouble with Aladin's suggestion...is anyone willing to enter this formula for me if I send you an email with my excel file attached to it?

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### graphpak

##### New Member
OK, so I've been told that what I'm looking for is called a dynamic named range...but i haven't the slightest clue as to how to set it up...Aladin I hear your the expert...can you please help me?

#### Zack Barresse

##### MrExcel MVP
graphpak said:
OK, so I've been told that what I'm looking for is called a dynamic named range...but i haven't the slightest clue as to how to set it up...Aladin I hear your the expert...can you please help me?

#### tactps

##### Well-known Member
Although Aladin is a far better exceller than I, it sounds to me that a sumif formula will work:

In A1, type the item number
In B1, type:
=sumif(range_item_sheet1,A1,range_quantity_sheet1)+sumif(range_item_sheet2,A1,range_quantity_sheet2)+sumif(range_item_sheet3,A1,range_quantity_sheet3)

e.g.
=SUMIF([Book5]Sheet1!\$A:\$A,A1,[Book5]Sheet1!\$B:\$B)+=SUMIF([Book6]Sheet1!\$A:\$A,A1,[Book6]Sheet1!\$B:\$B)+=SUMIF([Book7]Sheet1!\$A:\$A,A1,[Book7]Sheet1!\$B:\$B)

#### HalfAce

##### MrExcel MVP
In response to your PM, here's what I came up with...
Here's an example of how you can do this with a VLOOKUP formula.
On the first (location) sheet, I named columns A & B (the entire cloumns) Location1
On the second sheet I named them Location2
The third sheet... Location3

Then, in all 3 sheets, in cell E1 I entered this formula:

Now, whatever item number I enter into D1 (in any of the 3 sheets), the total quanity of that item from all 3 sheets returns in E1. If the item is missing from one or more of the sheets, you get a message telling you so.

We can do it with VBA too, but this formula seems to be working fine for me.

(I don't know which of all the formulas offered would be more efficient, but with my understanding of formulas, it probably ain't gonna be mine!

This what you're looking for?
Dan

#### Brian from Maui

##### MrExcel MVP
First you need to Define your worksheets. Go to Insert/Name/Define, in the Names in Workbook, enter SheetList and in the refers to window enter,

={"sheet1","sheet2","sheet3"} ..... sub your real sheet names for sheet 1 etc.

Now enter in your summary sheet,

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

Where A2:A10 houses the part numbers, A1 houses the part number you're searching for and B2:B10 the range to sum.

#### Todd Bardoni

##### Well-known Member
Wow! Brian, that is a fantastic solution! I've never thought of naming an array like that before. Fantastic!

Edit:- Must mention that the formula credit goes to Aladin of course...but I love the sheet name array as a defined name!

#### Brian from Maui

##### MrExcel MVP
Todd Bardoni said:
Wow! Brian, that is a fantastic solution! I've never thought of naming an array like that before. Fantastic!

Hey Todd,

It ain't me...it's Aladin. I use something similar, but not for summing.

Replies
3
Views
2K
Replies
4
Views
317
Replies
3
Views
206
Replies
1
Views
309
Replies
8
Views
595

1,182,191
Messages
5,934,177
Members
436,934
Latest member
TenDollarTypo

### 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.

### Which adblocker are you using?

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

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