SOLVED

# Sum and Subtract multiple cells with blank values without having the #Value Error

Copper Contributor

# Sum and Subtract multiple cells with blank values without having the #Value Error

Hello folks!

Once again I need your precious help.

I have a sheet where I need to sum and subtract various amounts on the same row. It would be very simple if it weren't for the "#Value" Error. In fact ALL the values I need to calculate belong to cells where formulas are present and sometimes the result of the formula is a blank cell.

Let me explain better:

The simple calculation I need to do is E2 + I2 - L3.

Sometimes all Three cells have a value in them but some other times E2 is empty, or I2 is empty or E2 and L2 are empty, or I2 and L2 are empty.

Is there a way to calculate without having the #value error?

Thank you very Much

4 Replies

# Re: Sum and Subtract multiple cells with blank values without having the #Value Error

Make the formulas return 0 instead of "".

# Re: Sum and Subtract multiple cells with blank values without having the #Value Error

Thanks Hans for your reply. Isn't there any other way as far as you know?

I'd prefer to keep the cells empty for various reasons (empty means a thing while 0 means another).
best response confirmed by Vesperwind (Copper Contributor)
Solution

# Re: Sum and Subtract multiple cells with blank values without having the #Value Error

Change

=E2 + I2 - L3

to

=N(E2) + N(I2) - N(L3)

or

=NUMBERVALUE(E2) + NUMBERVALUE(I2) - NUMBERVALUE(L3)

# Re: Sum and Subtract multiple cells with blank values without having the #Value Error

Thank you very much
1 best response

Accepted Solutions
best response confirmed by Vesperwind (Copper Contributor)
Solution

# Re: Sum and Subtract multiple cells with blank values without having the #Value Error

Change

=E2 + I2 - L3

to

=N(E2) + N(I2) - N(L3)

or

=NUMBERVALUE(E2) + NUMBERVALUE(I2) - NUMBERVALUE(L3)