SUM Column and Ignore #N/A Errors

Usually, the best way is to identify why these cells have errors and fix them. If these cells are formulas that are returning errors, the formulas can usually be modified so that they won't return errors.

So, are these errors the results of formulas? If so, what are the formulas?

I found the way to fix the problem:

=SUM(IF(ISNUMBER(J2:J2500),J2:J2500))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER

___________________________________
I know how to make the errors go away with something like:
IF(ISERROR(G15/G14),#N/A,(G15/G14))
--but in this particular instance--- the errors are a good thing (an indicator) that we actually (want) to see --- because it points to a mainframe report that has either "no data" in places where it SHOULD BE......or points to a TYPO in the main frame field that needs corrected.....

Regardless, the user still wanted the ability to quickly calculate the total list to get an approximate sum ( Value of merchandise ).....aside from the small batch of items that need re-worked.....then, they'll go back later in the day and fix any items that are generating bad results.... Thanks for taking a second to look at it --- Hopefully, this will help others with similar scenarios...

I know how to make the errors go away with something like:
IF(ISERROR(G15/G14),#N/A,(G15/G14))
--but in this particular instance--- the errors are a good thing (an indicator) that we actually (want) to see --- because it points to a mainframe report that has either "no data" in places where it SHOULD BE......or points to a TYPO in the main frame field that needs corrected.....

Click to expand...

Just something else to keep in mind, here is another way to go about it.

You can use the IS functions (ISNA, ISERROR, etc) to get rid of the errors like you suggested, and in the case of an error, return text like "ERROR!!!". The you can use a normal SUM formula (unlike the error codes you were getting, the SUM function ignores text entries like "ERROR!").

ChrisOK said:

I found the way to fix the problem:

=SUM(IF(ISNUMBER(J2:J2500),J2:J2500))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

...

Click to expand...

=SUMIF(J2:J2500,">0")

will suffice if J2:J2500 does not house any negative numbers.

Otherwise:

=SUM(SUMIF(J2:J2500,{"<0",">0"}))