How to change #VALUE! to 0 in formaula or Change all Non Value Cells to Zero

[craigy111]

Good afternoon all,

I am very frustrated as everything i have tried has failed so let me explain:

In Column E Row6 i have the following formula:

=IF(ISNUMBER(SEARCH("WON",Jan!A6)),Jan!G6,"") - Working Good

In Column J Row6 I have the following formula:

=SUBSTITUTE(SUBSTITUTE(E6,"£","+"),",","") - Working Good

In Column O I have inserted a formula using DEFINE NAME in Formulas Tab:

=EVALUATE(DATA!$J$6:$J$96) - PROBLEM

Now the problem that i am having is that the EVALUATE is returning #VALUE in NON VALUE Cells where only a formula lives.

I require to SUM Column O, as there is a #VALUE! this is not possible.
All numerical values

Is there anyway to either SUM Column O ignoring #VALUE!?
OR
Anyway to change the formula for EVALUATION to incorporate an ignore NO VALUE cells?
OR
Is there away to change #VALUE! to 0? or All Non Value Cells to 0?
OR
Any other suggestions?

I have tried VLOOKUP with no success.
I'm probably missing something simple but my head is fried.

Thanks to all for any pointers, advice or solutions

 

[Leo Skywalker]

=IFERROR(EVALUATE(DATA!$J$6:$J$96),0)
Will return zero.

 

[craigy111]

Leo,

Thanks that works a treat.

 

[Leo Skywalker]

You're welcome.
Glad to help.