Forum Discussion
Solved
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
Modify the VLOOKUP by adding IFERROR. The correct syntax is illustrated here.
=IFERROR(VLOOKUP($A5,Source,2,0),"")
Please also understand that the answers we've given you are not the only way to accomplish something like this. One of the wonders of Excel is that there are always multiple routes to the same destination; which of those routes is ideal can depend a lot on the bigger picture. What @Riny_van_Eekelen and I have demonstrated here are a couple of the more basic ways to retrieve a value from another table.
4 Replies
Make the formulas return 0 instead of "".
- 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).Change
=E2 + I2 - L3
to
=N(E2) + N(I2) - N(L3)
or
=NUMBERVALUE(E2) + NUMBERVALUE(I2) - NUMBERVALUE(L3)
