Return total instead of value if the lookup is showing zero

[Jamesorme]

I have a lookup in column G & H, when the value us zero in column G my simple sum in column L returns #value! instead of the total. What do I need to add to fix this please?

 

[Joe4]

I am guessing one of your apparently "blank" cells referenced in the formula is not actually blank, but a single space or "", which is considered text, which will result in a #VALUE error when used in addition or subtraction. Change your formulas so the return 0 instead of a space or "" and it should work.

If you do not wish to see the zeroes, you can always hide them with Custom Formatting or Conditional Formatting.

 

[Alex Blakenburg]

As @Joe4 pointed out its better to clean up the data. If for some reason that doesn't work for you, then this would get you out of trouble:

=N(H7)-N(G7)+N(K7)-N(J7)