Solved Formula Frustration

[Charles Williams-SC]

I have a problem with a formula that I know the Excel experts here can help me with.

In the attached spreadsheet I want to add H13 & J13 together and put it in K13. I tried to combine the formulas in C15:C18 into one formula but I get a #Value error.

I broke down the different parts of the formula in H14:K14 to make sure every part of the formula is working correctly.

The weird thing is, when I reference the cells H14:K14 (See L14) everything works fine.

I know it's probably just a missing closing bracket or something but s very frustrating. Any help from the experts here would be greatly appreciated.

BTW: I am using Excel 2019

 

[Ranman256]

colon does not combine , use: =A1 & A2

 

[Gasman]

OK, I'll bite.

Why can you not use H13+J13 ?

Also I have never used the IF() function without supplying values for both True and False?

 

[Charles Williams-SC]

colon does not combine , use: =A1 & A2

In L14 I did =H14 & I14 & J14 K14

 

[Charles Williams-SC]

OK, I'll bite.

Why can you not use H13+J13 ?

Thanks for the quick response. If either field is empty I get an error message (#Value). I want it to show me the total if either or both fields have a number in it.

I'm sorry, I meant to stated that in the original post.

 

[Isaac]

@Charles Williams-SC
The formula didn't work because you were trying to fully CLOSE each IF statement with closing parenthesis--they should have been left open, to move on to the False portion of each If formula. Then close them all successively at the very end.

Correct formula which works:

=IF(AND(H13="",J13=""),"",IF(AND(H13<>"",J13=""),H13,IF(AND(H13="",J13<>""),J13,IF(AND(H13<>"",J13<>""),SUM(H13,J13)))))

 

[Charles Williams-SC]

@Charles Williams-SC
The formula didn't work because you were trying to fully CLOSE each IF statement with closing parenthesis--they should have been left open, to move on to the False portion of each If formula. Then close them all successfully at the very end.

Correct formula which works:

=IF(AND(H13="",J13=""),"",IF(AND(H13<>"",J13=""),H13,IF(AND(H13="",J13<>""),J13,IF(AND(H13<>"",J13<>""),SUM(H13,J13)))))

Thanks Isaac for the completed formula.

One problem though, When I put the formula in it came up blank. It don't seem like it's getting past the first if statement. I want it to show me the total if either or both fields have a number in it. Should the first if statement be at the end?

Thanks for all your help.

 

[Isaac]

Thanks Isaac for the completed formula.

One problem though, When I put the formula in it came up blank. It don't seem like it's getting past the first if statement. I want it to show me the total if either or both fields have a number in it. Should the first if statement be at the end?

Thanks for all your help.

Hmm. The precise formula I posted came up fine with $30, when I used it in a downloaded copy of the very same file you posted ..

But honestly, without looking too much further into it, I would probably simplify that whole formula to:

=IF(OR(H13="",J13=""),"",SUM(H13,J13))

 

[Gasman]

Thanks Isaac for the completed formula.

One problem though, When I put the formula in it came up blank. It don't seem like it's getting past the first if statement. I want it to show me the total if either or both fields have a number in it. Should the first if statement be at the end?

Thanks for all your help.

Works for me?

 

[Charles Williams-SC]

Works for me?

OK, my Excel doesn't like me! I'll look at it further and let you know. I will also try the other formula.

Thanks a lot for all your help

 

[Charles Williams-SC]

I'm sorry, it was a EBTLAC error. (ErrorBetweenTheLaptopAndChair).

The formula provided worked perfectly.

BTW: The Or statement would not work (Unless it was nested further) because most of the time either field is blank thus causing the field to show blank.

Thanks again for all the help!

 

[Isaac]

OK, my Excel doesn't like me! I'll look at it further and let you know. I will also try the other formula.
Thanks a lot for all your help

Well, it's a Monday. Good luck!

 

[Isaac]

Oh, our posts crossed.
Glad to hear you got it working! Yes sorry, on my post that suggested OR, I forgot that if one was blank you still wanted to Sum the other - my bad. Apparently my brain is still getting up and running.