Thread: Subtotal range of values that include errors

Please would someone tell me the syntax of a formula to return the total of values of numerical entries in a range of cells, some of which cells individually return the error state #N/A? Ideally I would like to use SUBTOTAL(9,...) rather than SUM() but I can live with SUM() if necessary.

I am using Excel 2003

I have tried
=SUM(range)
=SUMIF(range,NOT(#N/A))
=SUMPRODUCT(range,NOT(ISNA(range))
=SUMPRODUCT(range*NOT(ISNA(range))

These all return #N/A as the total. This is not desired. I want the #N/A values to be ignored in the summing process.

Thanks

Try something like:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(range,ROW(range)-MIN(ROW(range)),,1)),--(ISNUMBER(range)))