# Help nesting multiple if's

#### ddub25

##### Well-known Member
=IF(E33="IB",
IF(AND(N33="Single",T33="W",OR(J33={"BF","BD"})),SUM((M33*R33)-(((M33-1)*R33)*S33)),
IF(AND(N33="Single E",T33="W",OR(J33={"BF","BD"})),SUM((R33/2)*M33)-(((M33-1)*(R33/2)*S33))+SUM(SUM(((M33-1)*25%)+1)*(R33/2))-((((M33-1)*25%)*(R33/2)*S33)),
IF(AND(N33="Single E",T33="P",OR(J33={"BF","BD"})),SUM(R33/2)*(((M33-1)*25%)+1)-((((M33-1)*25%)+1)*(R33/2)-(R33/2))*S33,
IF(AND(N33="Single",T33="W"),SUM(M33*R33),
IF(AND(N33="Single E",T33="W"),SUM((R33/2)*M33)+SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single E",T33="P"),SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single",T33="L"),0,
IF(AND(N33="Single E",T33="L"),0,
IF(AND(N33="Single",T33="V"),R33,
IF(AND(N33="Single E",T33="V"),R33,
"Error")))))))))),"Error")

=IF(E33="FB",
IF(AND(N33="Single",T33="W",OR(J33={"BF","BD"})),SUM((M33*R33)-(((M33-1)*R33)*S33)),
IF(AND(N33="Single E",T33="W",OR(J33={"BF","BD"})),SUM((R33/2)*M33)-(((M33-1)*(R33/2)*S33))+SUM(SUM(((M33-1)*25%)+1)*(R33/2))-((((M33-1)*25%)*(R33/2)*S33)),
IF(AND(N33="Single E",T33="P",OR(J33={"BF","BD"})),SUM(R33/2)*(((M33-1)*25%)+1)-((((M33-1)*25%)+1)*(R33/2)-(R33/2))*S33,
IF(AND(N33="Single",T33="W"),SUM(M33*R33),
IF(AND(N33="Single E",T33="W"),SUM((R33/2)*M33)+SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single E",T33="P"),SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single",T33="L"),0,
IF(AND(N33="Single E",T33="L"),0,
IF(AND(N33="Single",T33="V"),R33,
IF(AND(N33="Single E",T33="V"),R33,
"Error")))))))))),"Error")

I have these two formulas that I'd like to put together. The first has multiple if statements nested inside =IF(E33="IB",
The second has if's nested inside =IF(E33="FB",
When I try and put them together as one formula it returns '#VALUE!'
Can anyone help with the correct syntax?
(Note: this is just an example as you'll notice that all the nested if's are the same in both formulas - these will be different. I am just trying to understand the syntax required for now).

Dan

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Jugantarji

##### Board Regular
Hi Dan

You can only have 7 If conditons in A nested If formula. Assuming you have your formulas in A1 and B1 you could replace something at the end of the formula where you have "Error" and instead put b1 to refer to B1

#### Andrew Poulsom

##### MrExcel MVP
Is it?

=IF(E33="IB", IF(AND(N33="Single",T33="W",OR(J33={"BF","BD"})),SUM((M33*R33)-(((M33-1)*R33)*S33)), IF(AND(N33="Single E",T33="W",OR(J33={"BF","BD"})),SUM((R33/2)*M33)-(((M33-1)*(R33/2)*S33))+SUM(SUM(((M33-1)*25%)+1)*(R33/2))-((((M33-1)*25%)*(R33/2)*S33)), IF(AND(N33="Single E",T33="P",OR(J33={"BF","BD"})),SUM(R33/2)*(((M33-1)*25%)+1)-((((M33-1)*25%)+1)*(R33/2)-(R33/2))*S33, IF(AND(N33="Single",T33="W"),SUM(M33*R33), IF(AND(N33="Single E",T33="W"),SUM((R33/2)*M33)+SUM(SUM(((M33-1)*25%)+1)*(R33/2)), IF(AND(N33="Single E",T33="P"),SUM(SUM(((M33-1)*25%)+1)*(R33/2)), IF(AND(N33="Single",T33="L"),0, IF(AND(N33="Single E",T33="L"),0, IF(AND(N33="Single",T33="V"),R33, IF(AND(N33="Single E",T33="V"),R33, "Error")))))))))),IF(E33="IB", IF(AND(N33="Single",T33="W",OR(J33={"BF","BD"})),SUM((M33*R33)-(((M33-1)*R33)*S33)), IF(AND(N33="Single E",T33="W",OR(J33={"BF","BD"})),SUM((R33/2)*M33)-(((M33-1)*(R33/2)*S33))+SUM(SUM(((M33-1)*25%)+1)*(R33/2))-((((M33-1)*25%)*(R33/2)*S33)), IF(AND(N33="Single E",T33="P",OR(J33={"BF","BD"})),SUM(R33/2)*(((M33-1)*25%)+1)-((((M33-1)*25%)+1)*(R33/2)-(R33/2))*S33, IF(AND(N33="Single",T33="W"),SUM(M33*R33), IF(AND(N33="Single E",T33="W"),SUM((R33/2)*M33)+SUM(SUM(((M33-1)*25%)+1)*(R33/2)), IF(AND(N33="Single E",T33="P"),SUM(SUM(((M33-1)*25%)+1)*(R33/2)), IF(AND(N33="Single",T33="L"),0, IF(AND(N33="Single E",T33="L"),0, IF(AND(N33="Single",T33="V"),R33, IF(AND(N33="Single E",T33="V"),R33, "Error")))))))))),"Error"))

There must be a better way of getting a result than using IF.

#### Jugantarji

##### Board Regular
Sorry just seen that they're identical apart from the first argument, try

=IF(OR(E33="FB",E33="IB"),
IF(AND(N33="Single",T33="W",OR(J33={"BF","BD"})),SUM((M33*R33)-(((M33-1)*R33)*S33)),
IF(AND(N33="Single E",T33="W",OR(J33={"BF","BD"})),SUM((R33/2)*M33)-(((M33-1)*(R33/2)*S33))+SUM(SUM(((M33-1)*25%)+1)*(R33/2))-((((M33-1)*25%)*(R33/2)*S33)),
IF(AND(N33="Single E",T33="P",OR(J33={"BF","BD"})),SUM(R33/2)*(((M33-1)*25%)+1)-((((M33-1)*25%)+1)*(R33/2)-(R33/2))*S33,
IF(AND(N33="Single",T33="W"),SUM(M33*R33),
IF(AND(N33="Single E",T33="W"),SUM((R33/2)*M33)+SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single E",T33="P"),SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single",T33="L"),0,
IF(AND(N33="Single E",T33="L"),0,
IF(AND(N33="Single",T33="V"),R33,
IF(AND(N33="Single E",T33="V"),R33,
"Error")))))))))),"Error")

#### ddub25

##### Well-known Member
Thanks Andrew, that's what I was after, just couldn't get the syntax right. How else can I ask these questions other than using IF?

Replies
12
Views
440
Replies
4
Views
159
Replies
1
Views
93
Replies
3
Views
62
Replies
3
Views
980

### Forum statistics

1,195,940
Messages
6,012,426
Members
441,699
Latest member ### 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