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
Bookmarks