Need help with nested IF statement

[leopardhawk]

I have a formula that is working but I want to modify it ever so slightly. I believe I need a nested IF statement but I continue to get the #VALUE error. Here is the working formula:

=IF(COUNT(B16,B20)=2,SUM(B16+B20),"")

I would like the formula to return B16 on its own if it contains data... here is my modified formula (which returns the error):

=IF(COUNT(B16)=1,B16,""),IF(COUNT(B16,B20)=2,SUM(B16+B20),"")

Any suggestions are much appreciated. Thanks!

 

[pjmorris]

how about this:

=IF(OR(B16="",B16=0),"-",B16+B20)

To see where you're error is coming from use 'Evaluate Formula' on the 'Formula Auditing' tab of the Formula Ribbon - I use it a lot!

HTH

 

[Joe4]

When you nest IF formulas, the nested part goes in the FALSE clause (every IF has exactly 3 arguments, the condition, the TRUE clause, and the FALSE clause).
You have nested incorrectly, as you tried to do the nesting after the FALSE clause, instead of in the FALSE clause.

Also, SUM(B16+B20) is unnecessary and redundant.
If you just want to add those two numbers, all you need is B16+B20.
If you want to sum the range B16 through B20, then you would use SUM like this: SUM(B16:B20)

 

[Yongle]

Try this

=IF(COUNT(B16,B20)=2,SUM(B16,B20),IF(COUNT(B16)=1,B16,""))

I presume that you want the sum if both contain a number, otherwise B16 value if only B16 contains a number

 

[leopardhawk]

@pjmorris @Joe4 @Yongle I appreciate all three posts. PJ, I am still receiving the #VALUE if there is nothing in B20. Joe, I was trying your suggestion on my own when Yongle's came in and his works exactly as I need it to! Thanks to all three of you for your efforts. I learn as I go along here.

 

[Joe4]

You are welcome.
Glad we were able to help!