# SolvedFormula Frustration (1 Viewer)

#### Charles Williams-SC

##### New member
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

#### Attachments

11.1 KB · Views: 23

#### Ranman256

##### Registered User.
colon does not combine , use: =A1 & A2

#### Gasman

##### Enthusiastic Amateur
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?

Last edited:

#### Charles Williams-SC

##### New member
colon does not combine , use: =A1 & A2

In L14 I did =H14 & I14 & J14 K14

#### Charles Williams-SC

##### New member
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

##### Lifelong Learner
@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:
Code:
``=IF(AND(H13="",J13=""),"",IF(AND(H13<>"",J13=""),H13,IF(AND(H13="",J13<>""),J13,IF(AND(H13<>"",J13<>""),SUM(H13,J13)))))``

Last edited:

#### Charles Williams-SC

##### New member
@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:
Code:
``=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?

#### Isaac

##### Lifelong Learner
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?

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:

Code:
``=IF(OR(H13="",J13=""),"",SUM(H13,J13))``

#### Gasman

##### Enthusiastic Amateur
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?

Works for me?

#### Charles Williams-SC

##### New member
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

##### New member
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

##### Lifelong Learner
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

##### Lifelong Learner
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.

Replies
8
Views
93
Replies
6
Views
147
Replies
6
Views
210
Replies
6
Views
170
Replies
0
Views
74