How do I get Excel to recognize N/A as a zero?

[slaneues]

Hello,

I am using a nested index(..match(..)) function to retrieve data that is related to other data. Only non-zero quantities are listed in my spreadsheet, but I want my function to return a zero instead of N/A if it cannot find what I am looking for. If this is not possible, I would at least like for my other formulas to recognize that N/A means zero.

For instance, let's say I have an inventory of cars. I have 2 red nissans, 3 green ones and no blue ones. Because there are no blue Nissans, when I look up how many blue Nissans I have, I will get N/A because my database does not have anything that matches "Blue" and "Nissan".

When I sum all of my Nissans, I also get N/A because I have N/A in a sum.

Any ideas?

Thanks again for all of the help.

Shannon

[ben_hensel]

well... if you don't mind doubling the number of lookups you do...

=IF(iserror(lookup), 0, lookup)

There is a more elegant IFERROR(lookup, 0) function, but that is not available in 2003.

Well, I might wrap the IF around the match, depending on how the data is set up.

[vlady]

if ever you need the #N/A values on those ranges and want to sum them.
a work around would be sumif

=SUMIF(A1:A10,"<>#N/A")

[slaneues]

That worked incredibly well.

Thanks!!

Shannon

[LDouble3]

Use IFERROR function