Ask Your Question

Sum empty cell returns #value (update)

asked 2019-01-03 00:22:41 +0200

JG101 gravatar image

updated 2019-01-03 01:37:58 +0200

Not sure if this has been ask before

I'm trying to have SUM of 3 components =SUM(H211-F211)*E211 when there's a component (square) that is empty it returns #value

How do I prevent this from happening?

Thank you for helping in advance....

Updated.... i tried =(H211-F211)E211 instead of =sum(H211-F211)E211 see attachment of error I still get #value error when I use formula C:\fakepath\value error Libreoffice 01-02-2019.JPG

/////////////////////////// updated

I found the solution .... by experimenting =IFERROR(SUM(H211-F211)*E211,0)

this replaced #value! (error) with 0.00 which i needed

edit retag flag offensive close merge delete


=SUM(H211-F211)*E211 isn't a sum of three components, but the product of a difference with a third operatand.
The usage of SUM() is just an obfuscation in this case. You should better use =(H211-F211)*E211.
Check your "empty cells". They surely aren't empty, but contain formulae returning zero-length strings or whitespace - or the whitespace as direct content.

Lupp gravatar imageLupp ( 2019-01-03 00:56:36 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-01-03 00:59:45 +0200

Lupp gravatar image

updated 2019-01-03 01:06:36 +0200

(What do you mean by "square"?)

The SUM() function ignores text, but neither the subtraction operator - nor the multiplication operator * do so. And empty text (an empty string returned by a formula) is text.

If an operand of an arithmetic operation is text and cannot be automatically converted to a number, you get the error alert #VALUE!. The empty text is not convertible. A reference to a BLANK cell ist converted to 0.

Text not being empty, but consisting of whitespace characters only also is not convertible to number.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-03 00:22:41 +0200

Seen: 84 times

Last updated: Jan 03