SUM of Multiple IF functions


I am trying to create a calculator that populates one of many tables in the worksheet, with the totals from various sumed columns in the other tables based on a yes/no question.  In a nutshell, if the answer is yes then take the sum from this cell and add it to that cell and if the answer is no - do nothing.  The issue comes when more than one yes/no question is needed.  I want to be able to say something like the below but its not working.

=SUM(IF(C4="Yes",SUM(J50,J5),do nothing),(IF(C5="Yes",SUM(J48,J5),do nothing)

If value1 = yes, add sum1 to sum0, else do nothing + if value2 = yes, add sum2 to sum0, else do nothing + ......




please upload excel file to understand clearly
Yerram Raja Shekar (rep: 2) Jul 23, '18 at 2:23 am
Add to Discussion


Selected Answer

You need nested IF() functions. That is where the first "do nothing" is replaced with another IF() function.

=IF($C4="Yes", SUM(J50,J5), IF($C5="Yes",SUM(J48,J5),0))

Only after both (all - you can nest 7 deep) IFs are evaluated the decision to default to "do nothing" can be implemented. Of course, "do nothing" isn't a valid result. The reasonable alternative is 0. Often "" is suggested instead but that isn't reasonable because it isn't a number and, therefore, inhibits all further calculation. Excel has several ways of preventing the display of zeroes if that is the intention. All of them are preferrable to "".

Since J5 is a recurring value in your formula there is another way of writing it.

=J5 + IF($C4="Yes",J50,IF($C5="Yes",J48,0)

As you see, it is shorter and perhaps easier to read and understand. However, after all of the above, I suspect that you actually look for a formula for J5 itself. This must be modelled on the second suggestion. You can replace J5 with a hard value or the result of another formula to which either J48 or J50 is added depending upon the condition of C4 and C5. However that wouldn't accommodate the possiblity that C4 and C5 are both "yes". For that contingency you would need to un-nest your two IFs.

[J5] =SUM($F$3:$F$15) + IF($C4="Yes",J50,0) + IF($C5="Yes",J48,0)

Remember to place the $ signs correctly if your formula should be copied to other rows and/or columns. Set the $ sign for columns if you want to copy to other rows, and for rows if you want to copy to other columns, or for both to do both.



Thanks! This is exactly what I needed.  I was putting the IF inside the SUM instead of closing off the SUM and adding + between the functions. Worked great, thanks again!
barneeze (rep: 2) Jul 22, '18 at 12:32 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login