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

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

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

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

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

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

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 wrote in message
...
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