Thread: Sum Values Only in a Range

Hello Everyone,

Is there a formula that i could write to sum only values in a cell even if there are error message in those cells? eg: Lets say in Column 'A' i have (120,50,25,-10,-52, #NA) I need a formula to sum the values only and ignore any text or error message.


Thanks,
Headley

If you have just #N/A then:

=SUMIF(A:A,"<>#N/A")

If you have multiple error values within the precedent range then use an Array but restrict the range (entire column references aren't permitted pre XL2007)

=SUM(IF(ISNUMBER(A1:A10000),A1:A10000))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)