Classing an #N/A as a zero value

[Fractured]

I've had a bit of a search and haven't been able to find anything relating to what I need to do.

Is there a way of creating a sum or vlookup formula that will consider any #N/A value returned as a zero? For instance:

=SUM(C2:C3)

Can I produce a result of zero (or 3, for arguments sake, if C2=#N/A and C3=3) if either or both cells contain an #N/A?

Alternatively:

=VLOOKUP($A2,Workings!$E:$M,2,FALSE)

Is there a way of returning a zero value (as opposed to #N/A) if the value in A2 doesn't appear within the defined range (ie Workings!E:M)?

I realise that a simple sum or vlookup formula will not do this but am hopeful that there may be an alternative.

Nick

 

[jimbojones]

What about something like:


=SUMIF(B2:B3,"<>#N/A")

Any use?


James

 

[Fractured]

My word. It's all so simple when you know how!

Thanks James. That's awesome mate.

Take it easy,

Nick

 

[ctrlaltdel]

Jimbo's code answers your first question.

Here is how I deal with your second question:

Alternatively:

=VLOOKUP($A2,Workings!$E:$M,2,FALSE)

Is there a way of returning a zero value (as opposed to #N/A) if the value in A2 doesn't appear within the defined range (ie Workings!E:M)?

In place of your forumula enter:
=if(isna(VLOOKUP($A2,Workings!$E:$M,2,FALSE)),0,VLOOKUP($A2,Workings!$E:$M,2,FALSE))

Now anytime the vlookup returns #N/A, the if formula will give you a 0.

Let me know if this does what you need.

 

[Aladin Akyurek]

Jimbo's code answers your first question.

Here is how I deal with your second question:..In place of your forumula enter:

=if(isna(VLOOKUP($A2,Workings!$E:$M,2,FALSE)),0,VLOOKUP($A2,Workings!$E:$M,2,FALSE))

...

http://www.mrexcel.com/board2/viewtopic.php?t=62102

 

[ctrlaltdel]

Actually, http://www.mrexcel.com/board2/viewtopic.php?t=48679

 

[Fractured]

Thank you all for your input. All of those suggestions are equally good and useful.

Thanks again,

Nick