Min Value based on two conditions

vranjit138

Board Regular
Joined
Dec 18, 2006
Messages
141
Hi

I working on two sheets
sheet 1 has Name, area, Unit Price and amount ,
in the sheet 2 i have the customer ID .. i require the minimum unit price based on the name and area.


There are multiple prices for each name and each name repeats many times

How can this be done

Regards
Ranjit
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

=MIN(IF(Sheet1!A2:A20=A2,IF(Sheet1!B2:B20=B2,Sheet1!C2:C20)))

where a2:a20 = NameRange

b2:b20 = AreaRange

c2:c20 = UnitPriceRange

Formula must be confirmed with Ctrl+Shift+Enter

HTH
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

Not sure if I understand your problem completely but something like this might be what you're after:
Book1
ABCD
1NameAreaPrice
2NameAArea1132
3NameBArea1132
4NameCArea1147
5NameAArea1173
6NameBArea1172
7NameCArea1112
8NameAArea2148
9NameBArea2177
10NameCArea2176
11NameAArea2155
12NameBArea2134
13NameCArea2154
14
15
16
17NameAArea1132
Sheet1


Formula must be entered with Ctrl+Shift+Enter.

Dom
 

Forum statistics

Threads
1,181,658
Messages
5,931,269
Members
436,785
Latest member
KingGideon

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