Nested if statements

[rplohocky]

Hello,
Can anyone tell me what is wrong with this formula? Also if you have a better way to achieve the same result that would be fine as well. The formula is supposed to add multiple cells on multiple sheets. I have a drop down cell set up, when the user chooses CAB1 then it would return CAB1's number, if the user chooses CAB2 then it would return CAB1 and CAB2 combined, if the user chose CAB3 then the it would return CAB1, CAB2 and CAB3. I have 9 CAB sheets. I know on older versions of Excel you could only do 7 nested if statements but the newer version can much more and I tested this with 10 nested statements and it worked fine.

=if(Combined!M31=Combined!P31,'CAB1'!D2,if(Combined!M31=Combined!P32,'CAB1'!D2+'CAB2'!D2,if(Combined!M31=Combined!P33,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2,if(Combined!M31=Combined!P34,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2,if(Combined!M31=Combined!P35,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2+'CAB5'!,if(Combined!M31=Combined!P36,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2+'CAB5'!D2+'CAB6'!D2,if(Combined!M31=Combined!P37,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2+'CAB5'!D2+'CAB6'!D2+'CAB7'!D2,if(Combined!M31=Combined!P38,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2+'CAB5'!D2+'CAB6'!D2+'CAB7'!D2+'CAB8'!D2,if(Combined!M31=Combined!P39,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2+'CAB5'!D2+'CAB6'!D2+'CAB7'!D2+'CAB8'!D2+'CAB9'!D2,0)))))))))

 

[ranman256]

rather that next IF's into a jumble, make a custom function. Easier to read.

 

[Joe4]

Instead of using NESTED IF statements, couldn't you simply do an IF statement for each sheet, i.e.
- If condition 1 is met, then add the value from sheet1, else add 0.
- If condition 2 is met, then add the value from sheet2, else add 0.
- If condition 3 is met, then add the value from sheet3, else add 0.
etc
and then just add them all up, in the structure of:

=IF(Condition1,...) + IF(Condition2,...) + IF(Condition3,...) + .... + IF(Condition9,...)

 

[rplohocky]

Instead of using NESTED IF statements, couldn't you simply do an IF statement for each sheet, i.e.
- If condition 1 is met, then add the value from sheet1, else add 0.
- If condition 2 is met, then add the value from sheet2, else add 0.
- If condition 3 is met, then add the value from sheet3, else add 0.
etc
and then just add them all up, in the structure of:

=IF(Condition1,...) + IF(Condition2,...) + IF(Condition3,...) + .... + IF(Condition9,...)

Hello Joe4,
I believe this is what I am doing. I need the formula to add pages based on what the user selects in the drop down. If they choose CAB5 it would then add up CAB's 1,2,3,4 and 5.

 

[Joe4]

I believe this is what I am doing. I need the formula to add pages based on what the user selects in the drop down. If they choose CAB5 it would then add up CAB's 1,2,3,4 and 5.

No, it is absolutely NOT doing that.
You have one long nested IF statement. I am talking about multiple, individual IF statements added together.

 

[rplohocky]

No, it is absolutely NOT doing that.
You have one long nested IF statement. I am talking about multiple, individual IF statements added together.

Joe4,
Its most likely me, I may not be explaining the entire purpose for the sheet. Each sheet has to have numbers in it because they are being added up on another sheet that has nothing to do with the formula that I need. I think O know what you are saying about individual IF's but I don't see a way to accomplish both by doing it that way. I having been mulling this over for several days now and i can't come up with another way to do it without using nested if statements.

 

[Joe4]

Something like this, as shown for the first four pages (note that they are all independent IF functions):

=IF(MID(M31,4,LEN(M31))+0>=1,'CAB1'!D2,0)
+IF(MID(M31,4,LEN(M31))+0>=2,'CAB2'!D2,0)
+IF(MID(M31,4,LEN(M31))+0>=3,'CAB3'!D2,0)
+IF(MID(M31,4,LEN(M31))+0>=4,'CAB4'!D2,0)
+...

Note that each IF formula is the same, except for the two numbers in red.