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{}.