Nested IF?

[Qazxswe]

I have a series of prices as; D4 = Item A, D5 = Item B D6 = Item C

I have a IF statement in J15 which reads =IF(H15="YES",$D$4+D$6$,0) so adding Item A + Item C

In I15, if this contains a "Yes", I want the above statement in J15 to add together all three items (so D4+D5+D6)

What is the best way to do this? I am trying to do a nested IF but it stops after the initial statement so just adding items A + C

 

[jasonb75]

If that is as far as the problem goes, then try =IF(H15="YES",$D$4+D$6$,IF(I15="Yes",SUM($D$4:$D$6),0))

If this is going to keep expanding with more and more items / prices, then you would need to look at alternatives to IF, which might mean redesigning your sheet.

 

[Qazxswe]

Thanks for quick response - yes that is as far as the problem goes but Excel saying there is an error with above formula? Sorry can't see where it is...

 

[jasonb75]

There is an error, I didn't notice your typo when I copied the short formula from your post. Check the position of the $ signs in red compared to the previous posts.

=IF(H15="YES",$D$4+$D$6,IF(I15="Yes",SUM($D$4:$D$6),0))

 

[Qazxswe]

Sorry - my bad and thanks again

It's not quite working still I'm afraid.

H15 = Yes & I15 = <blank> Result: Item A + C = Correct

H15 = Yes & I15 = Yes Result: same as above value = Incorrect

H15 = <blank> & I15 = Yes Result: A + B + C =Correct

Sorry if I wasn't clear' H15 will usually be "yes" = adding "yes" to I15 should add the item 2 increment

Makes sense?

 

[Qazxswe]

Edit - solved another way. Sum the A + C price and used a single IF to total up dependant if the Item B cell was a "yes"

Thanks again!

 

[jasonb75]

H15 = Yes & I15 = Yes Result: same as above value = Incorrect

Are you saying that the formula result is the same as above, or that it should be the same as above?

 

[jasonb75]

Edit - solved another way. Sum the A + C price and used a single IF to total up dependant if the Item B cell was a "yes"

That was one of my thoughts, but I dismissed it as your original post implied a zero result if neither had "Yes"

 

[Qazxswe]

That was one of my thoughts, but I dismissed it as your original post implied a zero result if neither had "Yes"

Which is was my expectation and the problem I just hit when thinking I had worked it out!

are you saying that the formula result is the same as above, or that it should be the same as above?

Yes = the it results in the value of Item A + C with no increase for B

 

[jasonb75]

Yes = the it results in the value of Item A + C with no increase for B

That still doesn't make it any clearer.

What is the correct result for "Yes" in both cells?