Compound if statement?

graphpak

New Member
Joined
Jan 24, 2005
Messages
2
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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?
 
Upvote 0
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?

Is the set up described in the quoted link not adaptable to your problem?
 
Upvote 0
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)
 
Upvote 0
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:
=IF(ISERROR(D1=VLOOKUP(D1,Location1,2,0)+VLOOKUP(D1,Location2,2,0)+VLOOKUP(D1,Location3,2,0)),"Item not found in at least one sheet.",IF(D1="","",VLOOKUP(D1,Location1,2,0)+VLOOKUP(D1,Location2,2,0)+VLOOKUP(D1,Location3,2,0)))

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! :LOL:

This what you're looking for?
Dan
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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