Ignoring #N/A in an Autosum range
 

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?

Ignoring #N/A in an Autosum range
 

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


"Jaye" wrote:

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?

Ignoring #N/A in an Autosum range
 

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)?

"Ron Coderre" wrote:

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


"Jaye" wrote:

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?

Ignoring #N/A in an Autosum range
 

See your other post.

Biff

"Jaye" wrote in message
...
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)?

"Ron Coderre" wrote:

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


"Jaye" wrote:

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?

Ignoring #N/A in an Autosum range
 

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",V LOOKUP(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

"Jaye" wrote:

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)?

"Ron Coderre" wrote:

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


"Jaye" wrote:

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?

Ignoring #N/A in an Autosum range
 

=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.

--
HTH

Bob

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

"charles44" wrote in message
...
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",V LOOKUP(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

"Jaye" wrote:

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)?

"Ron Coderre" wrote:

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


"Jaye" wrote:

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?

Ignoring #N/A in an Autosum range
 

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

On Mon, 9 Jul 2007 04:54:02 -0700, charles44
wrote:

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

"Jaye" wrote:

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)?

"Ron Coderre" wrote:

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


"Jaye" wrote:

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?