Solved Formula Frustration (1 Viewer)

Charles Williams-SC

New member
Local time
Today, 07:53
Joined
Jul 13, 2020
Messages
10
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

  • Pricing Speadsheet - Question.zip
    11.1 KB · Views: 187

Ranman256

Well-known member
Local time
Today, 07:53
Joined
Apr 9, 2015
Messages
4,339
colon does not combine , use: =A1 & A2
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:53
Joined
Sep 21, 2011
Messages
14,051
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
Local time
Today, 07:53
Joined
Jul 13, 2020
Messages
10
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
Local time
Today, 04:53
Joined
Mar 14, 2017
Messages
8,738
@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
Local time
Today, 07:53
Joined
Jul 13, 2020
Messages
10
@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?

Thanks for all your help.
 

Isaac

Lifelong Learner
Local time
Today, 04:53
Joined
Mar 14, 2017
Messages
8,738
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:

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

Gasman

Enthusiastic Amateur
Local time
Today, 11:53
Joined
Sep 21, 2011
Messages
14,051
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?
1594661899938.png

1594661926949.png

1594661961377.png

1594662037843.png
 

Charles Williams-SC

New member
Local time
Today, 07:53
Joined
Jul 13, 2020
Messages
10
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
Local time
Today, 04:53
Joined
Mar 14, 2017
Messages
8,738
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.
 

Users who are viewing this thread

Top Bottom