#VALUE! Occuring in formula result

[gacollege]

I have a formula in cell A1:

=IF(ISNA(VLOOKUP(B9,ALL!A6:E1091,5,FALSE)=TRUE),"",VLOOKUP(B9,ALL!A6:E1091,5,FALSE))

I used this formula to eliminate the #N/A that would appear if no value
is found in the vlookup.

The result in A1 was #N/A, but now it is blank due to ISNA formula.
However, now if I add cell A1 with others cells. =SUM(A1:A10), it
returns #VALUE!.

The blank cell as a result of the ISNA formula is causing this cell not
to add.

Please help.....Thanks,

Richard

 

[Niek Otten]

Use a zero instead of "".

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Frank Kabel]

Or if you don't want to have '0' in your sheet you may try
=SUM(VALUE(A1:A10))
entered as array formula (CTRL+SHIFT+ENTER)

Frank

 

[gacollege]

=IF(ISNA(VLOOKUP(B9,ALL!A6:E1091,5,FALSE)=TRUE),"0",VLOOKUP(B9,ALL!A6:E1091,5,FALSE)
- That works, wow such an easy fix to what I thought was complex.

In addition, thanks Frank your suggestion works also !

 

[Dave Peterson]

You may want to use 0 instead of "0".

Since =sum() ignores text and ignoring text is pretty much the same as adding 0,
it shouldn't matter much.

But if you ever need to go back to do more arithmetic, you might be happier with
the number 0--not the text "0".

(and if you want, you could drop the =true from your formula. It could save you
minutes over your lifetime!)