Ignoring #N/A in an Autosum range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jaye - 16 Mar 2006 18:42 GMT

I need to be able to add data in a column, and some of the cells in that
column have an #N/A in it, resulting from a VLOOKUP. How do I add the data in
the column, and have it ignore the #N/A?

Reply to this Message

Ron Coderre - 16 Mar 2006 19:03 GMT

Try something like this:

For values in A1:A10

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

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

> I need to be able to add data in a column, and some of the cells in that
> column have an #N/A in it, resulting from a VLOOKUP. How do I add the data in
> the column, and have it ignore the #N/A?

Reply to this Message

Jaye - 16 Mar 2006 19:14 GMT

Awesome - worked perfectly!! Thanks so much.

Another question - if I do a VLOOKUP and the resulting answer is the #N/A
again, how can I get the result to be a zero (0)?

> Try something like this:
>
[quoted text clipped - 13 lines]
> > column have an #N/A in it, resulting from a VLOOKUP. How do I add the data in
> > the column, and have it ignore the #N/A?

Reply to this Message

Biff - 16 Mar 2006 20:15 GMT

See your other post.

Biff

> Awesome - worked perfectly!! Thanks so much.
>
[quoted text clipped - 20 lines]
>> > data in
>> > the column, and have it ignore the #N/A?

Reply to this Message

charles44 - 09 Jul 2007 12:54 GMT

I have the same concern so instead of creating a new thread, I thought i'd
highjack this one. I have:

=IF(ISERROR(VLOOKUP(D13,$B$2:$C$12,2,FALSE)),"0",VLOOKUP(D13,$B$2:$C$12,2,FALSE))

to show a #n/a as a "0" (which is what I want), but there must be a better
way to do this. Is there?

If so or not, how would I apply them to multiple cells (column edit) with
different references (other then the "D13", also say, "D14",etc.) in a single
edit?

Thanks, Charles

> Awesome - worked perfectly!! Thanks so much.
>
[quoted text clipped - 18 lines]
> > > column have an #N/A in it, resulting from a VLOOKUP. How do I add the data in
> > > the column, and have it ignore the #N/A?

Reply to this Message

Bob Phillips - 09 Jul 2007 13:24 GMT

=SUM(IF(NOT(ISERROR(E1:E4)),E1:E4))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have the same concern so instead of creating a new thread, I thought i'd
> highjack this one. I have:
[quoted text clipped - 35 lines]
>> > > data in
>> > > the column, and have it ignore the #N/A?

Reply to this Message

Gord Dibben - 09 Jul 2007 21:35 GMT

Select the range of cells and run this macro.

Sub ErrorTrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & myStr & "),0," & myStr & ")"
End If
End If
Next
End Sub

I personally would use ISNA rather than ISERROR in a Lookup formula so' not to
mask other errors.

Gord Dibben MS Excel MVP

>I have the same concern so instead of creating a new thread, I thought i'd
>highjack this one. I have:
[quoted text clipped - 32 lines]
>> > > column have an #N/A in it, resulting from a VLOOKUP. How do I add the data in
>> > > the column, and have it ignore the #N/A?

Reply to this Message