How do I sum a named range of values that contains errors?

Question

I have an Excel spreadsheet which imports data from another source that I need to run a calculation on. The data I need to work with is in a named range - it happens to be in the range C12:C36 - but it's called "SumData".

The values in this range contain a number of errors currently including #NUM! and #N/A, but could potentially contain other errors. I need to sum these values.

I understand how to do the sum formula:

=SUM(SumData)

Or

=SUM(C12:C36)

I can also use IFERROR to check for errors:

=IFERROR(C12:C36, 0)

But IFERROR only appears to check the current corresponding row to the formula I'm entering. i.e. if my formula is being entered in D12, it only checks for error on row C12, and returns the value (or 0 if C12 contains an error).

If I combine the two to do:

=SUM(IFERROR(SumData,0))

I don't get a valid return value. It basically gives me the nearest corresponding row - for instance:

    C      D
    -----------
12  #NUM!  
13  2      =SUM(IFERROR(SumData,0)) = 2 (I would expect this to produce 48)
14  5
15  7
16  #N/A
17  23
18  6
19  5

What am I doing wrong?

Answer 1

=SUM(IF(ISNUMBER(SumData),SumData))

entered as an array formula... ie using CTRL+Shift+Enter and NOT just Enter. If you do it correctly then Excel will put curly brackets around the formula{}.

Answer 2

Try Use this Formula if you tend to add a range of cells that contains error:

=SUMIFS(sumrange,criteria range1,"<>"&"#NAME?",criteria range2,"<>"&"#N/A",criteria range3,"<>"&"NUM!")

Since you don't know which cell has the error, then it is advisable to use the criteria ranges same as the sumrange.