Finding lowest cost price with multiple criteria

**jimbokeep** · 06-30-2010, 10:51 AM

I have a number of suppliers who all supply the same product and manufacture the product in the same city. I want to be able to find the lowest cost for a specific product from a manufacturer in a specific city.

Here is a basic example of the table of information that i have:
Part Code Supplier Location Unit Cost
AAAA Factory 1 Xiamen $5
AAAA Factory 2 Xiamen $10
AAAA Factory 3 Xiamen $15
AAAA Factory 4 Shenzhen $5
AAAA Factory 5 Shenzhen $10
AAAA Factory 6 Shenzhen $15

What formula must I use to find the lowest cost price for Part Code AAAA, in Xiamen?

**royUK** · 06-30-2010, 11:01 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**jimbokeep** · 06-30-2010, 11:06 AM

thanks for the advice, i'll do that.

**pb71** · 06-30-2010, 11:16 AM

Please see attached.

**jimbokeep** · 06-30-2010, 11:24 AM

I've created a very basic spreadsheet to show the data that i am trying to use and the end result that i am after.
On the page titled Pricing (FOB & Landed) there is a table containing part codes by supplier, location and cost.
What i'm trying to do is insert a formula into the next two worksheets, to find which is the lowest cost for each specific product, from each location where the goods are manufactured.
This will then allow we to calculate what my final/best landed cost in the UK is.

Hopefully this explains in a bit more detail exactly what i'm trying to do.

Thanks.

**jimbokeep** · 06-30-2010, 11:25 AM

that looks spot on. thanks very much.

**pb71** · 06-30-2010, 11:33 AM

I've updated your workbook with a formula for the Xiamen worksheet. The same formula can be applied to the other worksheet.

EDIT: You could also use a Pivot Table (please see attached).

**jimbokeep** · 06-30-2010, 11:56 AM

Thanks pb71. Your first example you sent was perfect and has solved the problem for me. I'd been trying to work that out for ages.
Plus thanks for the hint about a pivot table. Cheers.

**shg** · 06-30-2010, 01:11 PM

You'd make life easier for yourself and pb71 if you don't type the dollar signs in col D, which makes them text (you can instead format those cells to have dollar signs).

Then the formula in I2 becomes

=MIN(IF(( $A$2:$A$7=$G$2) * ($C$2:$C$7=$H$2), $D$2:$D$7))

... which still must be confirmed with Ctrl+Shift+Enter.