• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 27
  • Last Modified:

Excel formula IFERROR

Excel IFERROR formula,

I want to add the sum of cells A1:G1 with iferror formula. Some of my current values are #N/A. How do I write the formula?

Conernesto
0
Conernesto
Asked:
Conernesto
  • 5
  • 3
  • 2
1 Solution
 
ConernestoAuthor Commented:
To clarify, I need a formula that will add the values in cells A1:G1. Some of the values in the range are #N/A.
0
 
ConernestoAuthor Commented:
Sorry, the actual cells to add are in a column. The cells are A1+A3+A6+A9+A12.

Cells A1, A3, A6, and A12 have amounts. Cell A9 has #N/A
0
 
Bill PrewCommented:
Try this:

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



»bp
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Bill PrewCommented:
WHoops, change of requirements, okay, try this...

=IF(ISNUMBER(A1),A1,0)+IF(ISNUMBER(A3),A3,0)+IF(ISNUMBER(A6),A6,0)+IF(ISNUMBER(A9),A9,0)+IF(ISNUMBER(A12),A12,0)

Open in new window


»bp
0
 
ConernestoAuthor Commented:
Your formula works with the range.  My values are in a column in different cells within the column.
0
 
Bill PrewCommented:
See my update...
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Why some cells have #N/A?
Remove them by tweaking the formulas at the first place to return a null string or a text string like "Not Found" and then the simple SUM formula will work as SUM will ignore the Text values and only sum the numeric values in the range.
0
 
ConernestoAuthor Commented:
I already added a lot of formulas and I did not use the iferror formula to avoid the #N/A. So I am trying to work around this.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Just wrap the formula which is returning #N/A with IFERROR like below...
=IFERROR(<Your Formula Here>,"")

Open in new window

0
 
ConernestoAuthor Commented:
Thank you very much.

Conernesto.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now