Multiple IF criteria

johnawebbhsd

New Member
Joined
Apr 4, 2003
Messages
47
I am trying to write a statement that will compare multiple criteria before returning a result, but having issues. Similair to Vlookup but looking at 2 cells first

Example I have 2 worksheets (sheet1, sheet2)

Sheet1 is for a City (Name in A1), Part#'s listed in A3-A?
Sheet2 has cities listed in A1-A800 each city has many rows, and parts in E part numbers may repeat, but only 1 per city, Qnty is in F...

Trying to write in C3 to look at A1 text (CITY) A3 (Part#), then compare to Sheet 2 and find where the match (CITY) ColumnB and (Part#) columnE, and return results from F if A1(sheet1) matches any B(sheet2) and if so, A3(sheet1) matches E(sheet2) of same row return F from that row....

Wow hope I said all that right.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try this formula copied down the column

=SUMPRODUCT(--(Sheet2!$A$1:$A$800=$A$1),--(Sheet2!$E$1:$E$800=$A3),Sheet2!$F$1:$F$800)

or perhaps a more efficient solution which requires a concatenation column.....

If you concatenate columns A and E in sheet2 by using this formula in G1

=A1&"#"&E1

copied down column you can then use this formula in sheet1 C3, also copied down the column

=INDEX(Sheet2!$F$1:$F$800,MATCH($A$1&"#"&$A3,Sheet2!$G$1:$G$800,0))
 

johnawebbhsd

New Member
Joined
Apr 4, 2003
Messages
47
Wow.. that worked great... I would have never even tried to use SUMPRODUCT... Not exactly sure why to use SUMPRODUCT for this formula.. when you have time, or if, could you explain the formula...

Thanks again.. worked great...
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Your problem is essentially a lookup, hence the alternative formula I suggested in my edited post above, but where your return values are numeric and there is only one match, as I believe is the case here, SUMPRODUCT will give the correct result.

The formula in effect sums all values in column F where column A matches your city and column E matches your part. In this case there should be only one match so the "sum" should just be of the one relevant figure.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,546
Messages
5,838,029
Members
430,526
Latest member
NiceGuyWithExcel2007

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
Top