getting the sum of a column with #N/A values in it

[Moy Emrick]

I created a VLookup formula to display the value of an item if it exists. If
the value doesn't exist, it displays an #N/A. 1) Can I modify this formula to
display a "0" instead of an #N/A? and 2) How can I total a column which only
excludes #N/A? A copy of my formula is below.

=VLOOKUP(B156,'2003'!B:T,3,0)

Thank you very much.
Moy Emrick

[Dave Peterson]

=if(iserror(yourvlookupformula),0,yourvlookupformula)

And if you show 0's, you won't need to worry about that sum formula.

But if you want...

=sum(if(isnumber(a1:a10),a1:a10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

Or if all your errors are #n/a's, you could use:

=SUMIF(A1:A10,"<>#n/a")
(not an array formula)

Moy Emrick wrote:
>
> I created a VLookup formula to display the value of an item if it exists. If
> the value doesn't exist, it displays an #N/A. 1) Can I modify this formula to
> display a "0" instead of an #N/A? and 2) How can I total a column which only
> excludes #N/A? A copy of my formula is below.
>
> =VLOOKUP(B156,'2003'!B:T,3,0)
>
> Thank you very much.
> Moy Emrick

--

Dave Peterson

[Biff]

Hi!

1.

=IF(COUNTIF(2003'!B:B,B156),VLOOKUP(B156,2003'!B:T,3,0),0)

2.

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

Biff

"Moy Emrick" <Moy [email protected]> wrote in message
news:[email protected]...
>I created a VLookup formula to display the value of an item if it exists.
>If
> the value doesn't exist, it displays an #N/A. 1) Can I modify this formula
> to
> display a "0" instead of an #N/A? and 2) How can I total a column which
> only
> excludes #N/A? A copy of my formula is below.
>
> =VLOOKUP(B156,'2003'!B:T,3,0)
>
> Thank you very much.
> Moy Emrick