# Multiple column scenarios

#### Caly

##### New Member
Hi I have multiple columns each with a section of data to feed into the criteria. 3 various prices that based on their 3 associated columns will have either yes or no. This results in multiple scenarios. Example is below of the formula but is there an easier way to write this formula?

=If(and(Price A=Yes,price b=yes,price c=yes), Min(price a, price b, price c),
If(and(Price a=no, price b=yes, price c=yes), min(price b, price c),
If(and(Price a=yes,price b=no,price c=yes), min(price a, price c),
If(and(price a=yes, price b=yes, price c=no),
Min(price a, price b),
If(and(Price a=yes,price b=no,price c=no), price a,
If(and(Price a=no,price b=yes,price c=no), price b,
If(and(Price a=no,price b=no,price c=yes), price c,
“Check”

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### AlanY

##### Well-known Member
try this

Book1
ABC
1A100Y
2B200N
3C300Y
4
5min100
Sheet1
Cell Formulas
RangeFormula
B5B5=MINIFS(B1:B3,C1:C3,"Y")
Cells with Data Validation
CellAllowCriteria
C1:C3ListY, N

#### AlanY

##### Well-known Member
with Excel 2013, try this

Book1
ABC
1A100Y
2B200N
3C300Y
4
5min100
Sheet1
Cell Formulas
RangeFormula
B5B5=MIN(IF(C1:C3="Y",B1:B3))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
C1:C3ListY, N

#### Caly

##### New Member
Thank you do much. But the options are across and not up and down. So price A, B and C with their yes or no results are across the rows in different columns and not on different rows. Is there a way to write the formula for this?

#### Caly

##### New Member

Thank you do much. But the options are across and not up and down. So price A, B and C with their yes or no results are across the rows in different columns and not on different rows. Is there a way to write the formula for this?

#### Attachments

• 51B1800E-44BB-4D20-8EFA-FA183EB6CC10.jpeg
252.4 KB · Views: 2

#### AlanY

##### Well-known Member
not the most elegant way, but check if that works for you

Book1
ABCDEFG
1Price APrice A OK?Price BPrice B OK?Price CPrice C OK?
25Yes6Yes7Yes5
35No6Yes7Yes6
45Yes6No7Yes5
55No6No7Yes7
65No6Yes7No6
75Yes6Yes7Yes5
Sheet1
Cell Formulas
RangeFormula
G2:G7G2=MIN(IF(B2="YES",A2,1000000),IF(D2="YES",C2,1000000),IF(F2="YES",E2,1000000))
Cells with Data Validation
CellAllowCriteria
D2:D7ListYes, No
F2:F7ListYes, No
B2:B7ListYes, No

#### Fluff

##### MrExcel MVP, Moderator

+Fluff v2.xlsm
ABCDEFGH
1
25Yes6Yes7Yes5
36no7no8Yes8
47No8Yes9Yes8
58no9Yes10No9
69No10no11yes11
Summary
Cell Formulas
RangeFormula
H2:H6H2=MIN(IF(C2:G2="Yes",B2:F2))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Caly

##### New Member
Thank you. I getting a #value! Error code unfortunately

There will be additional data set columns between the price to select the minimum of and if the price says yes or no. The formula it works and yields the correct answer but is just very long. So was trying to write something simpler.
The min of suggestions are great but I am getting an error code

attached is an image

#### Attachments

130.1 KB · Views: 2

#### Fluff

##### MrExcel MVP, Moderator
Thank you. I getting a #value! Error code unfortunately
Who are you talking to here?

Also can you please post some sample data to the board using the XL2BB add-in, we cannot manipulate an image.

#### AlanY

##### Well-known Member
you need to adjust the formula with the extra columns like this

Book1
ABCDEFGHIJKLM
1Price APrice A OK?Price BPrice B OK?Price CPrice C OK?
25Yes6Yes7Yes5
35No6Yes7Yes6
45Yes6No7Yes5
55No6No7Yes7
65No6Yes7No6
75Yes6Yes7Yes5
Sheet1
Cell Formulas
RangeFormula
M2:M7M2=MIN(IF(D2="YES",A2,1000000),IF(H2="YES",E2,1000000),IF(L2="YES",I2,1000000))

Replies
7
Views
143
Replies
2
Views
112
Replies
5
Views
111
Replies
9
Views
126
Replies
1
Views
98

1,126,980
Messages
5,621,946
Members
415,869
Latest member
LWSkinner

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