# Nested IF And with Or statement

#### Caly

##### New Member
Hi I have a statement where I keep getting an error. How do I write this formula?

column A is for a unit of issue conversion where is may say 2.
columns B, D, and F will have prices for different products
Column H will have the product to use and column I will have the price based on the product in column I
For the column I formula I am trying to say if Column A is not blank and Column H either says one of the two product types that may be in Column B to multiply the price by the column A amount and if column A is blank then just use the price for the product.

here is an image of what I am trying

#### Attachments

• image001.png
21 KB · Views: 6

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Caly

##### New Member

=if(and(or(A4<>"",H4="Apple",H4="Orange"),B4*A4,B4)),IF(AND(A4<>"",H4="Apple"),B4*A4,IF(AND(A4<>"",H4="Pear"),D4*A4,IF(AND(A4<>"",H4="Grape"),F4*A4,IF(H4="Apple",B4,IF(H4="Pear",D4,F4)))))

#### Fluff

##### MrExcel MVP, Moderator
Thanks for that.
Excel Formula:
``=IF(OR(H4="Apple",H4="Orange"),B4,IF(H4="Pear",D4,IF(H4="Grape",F4,"")))*IF(A4="",1,A4)``

#### Caly

##### New Member

Thank you so much...that worked perfect.

If the u it conversion varies between the products, how can that be written in the formula?

so I’m trying to see if there is a different unit conversion by product how can that be written?

column a would be the unit conversion for apple or orange in column b
But then I need to have a separate unit conversion column for Pear and another for Grape

the unit conversion for pear would be in column d and for grape would be in column G

I was trying this but it is not working for me.
IF(OR(J4="Apple",J4="Orange"),B4*IF(A4="",1,A4),IF(J4="Pear",E4*IF(D4="",1,D4),IF(J4="Grape",H4,*IF(G4="",1,G4)

#### Caly

##### New Member

 Unit Conversion​ Apple or Orange​ Amount​ Unit Conversion​ Pear​ Amount​ Unit Conversion​ Grape​ Amount​ Final Type​ Final Price​ Formulas​ 2​ \$5.00​ EA​ 3​ \$6.00​ EA​ 5​ \$7.00​ EA​ Apple​ \$10.00​ IF(AND(A2<>"",H2="Apple"),B2*A2,IF(AND(A2<>"",H2="Pear"),D2*A2,IF(AND(A2<>"",H2="Grape"),F2*A2,IF(H2="Apple",B2,IF(H2="Pear",D2,F2))))) ​ \$8.00​ PK​ ​ \$9.00​ PK​ ​ \$10.00​ PK​ Grape​ \$10.00​ IF(AND(A3<>"",H3="Apple"),B3*A3,IF(AND(A3<>"",H3="Pear"),D3*A3,IF(AND(A3<>"",H3="Grape"),F3*A3,IF(H3="Apple",B3,IF(H3="Pear",D3,F3))))) 2​ \$2.00​ PK​ 4​ \$3.00​ PK​ 6​ \$4.00​ PK​ Orange​ \$4.00​ IF(OR(J4="Apple",J4="Orange"),B4*IF(A4="",1,A4),IF(J4="Pear",E4*IF(D4="",1,D4),IF(J4="Grape",H4,*IF(G4="",1,G4)
Thank you so much. Here it is

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=IF(OR(J4="Apple",J4="Orange"),B4*IF(A4="",1,A4),IF(J4="Pear",E4*IF(D4="",1,D4),IF(J4="Grape",H4*IF(G4="",1,G4))))``

#### Caly

##### New Member
That worked ... thank you so much for your help

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
6
Views
78
Replies
7
Views
287
Replies
4
Views
62
Replies
3
Views
77
Replies
3
Views
433

1,130,175
Messages
5,640,607
Members
417,156
Latest member
Ciupanezul21

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