Eliminating #value when there are blank cells in calculation

[Zoycyte]

Hello,

The senario I have is that I want to have a formula calculate even when there is a blank cell in the calculation.

I have 3 canned reports that I drop into excel & format. From there I have a summary by description worksheet with vlookups & a calculation of all 3 reports.

vlookup:
=IFERROR(VLOOKUP(C20,'CPR240 - B'!$J:$K,2,FALSE),"")

Calculation (here is where I am getting the #value error)
=D20+E20-F20

I know the easiest thing to do would be to change the vlookup to return 0 instead of blank but I don't want it to be confused with something that is actually a zero dollar value. I would rather have it blank or something else you can suggest because this will show us that one of the reports is missing that field & we can log an incident with our group to have it updated. In the meantime I still need it to return a dollar value based on the other non-blank cells.

Appreciate any assistance.

Zoycyte

 

[Brian from Maui]

Try using SUM(D20,E20,-F20)

 

[Zoycyte]

Hi Brian,

Beauty. It worked. I think everything I tried was overly complicated thinking when really I should have gone back to basics.
Thanks so much.

 

[Brian from Maui]

Glad I could help

 

[Jonmo1]

Try using SUM(D20,E20,-F20)

FYI,
That would still result in #Value! if F20 was a formula returning ""
Because of the -

You'd have to do
SUM(D20,E20,-SUM(F20))

But since the OP said it worked, then F20 must not have been one of the formulas returning "".

 

[Brian from Maui]

Thanks for the correction Jon.

Must've lucked out with the answer