multipule calculations in a single column

[mexmex]

Trying to total sum a range of cells in a column and subtract from a total in
another cell in the same column, getting a value error

ex sum cell a10 to a20 then subtract from the number in a5
excel 2007

 

[Otávio Alves Ribeiro]

Hi there.
You may want to try:

=SUM(A10:A20)-IF(ISNUMBER(A5),A5,0)

Anyway, you should be sure that the value in A5 IS a number and not a
string/text that 'SEEMS' to be a number. Tip: text/string are left aligned by
default.

Regards,
Otávio

 

[Jacob Skaria]

Shouldnt be the other way around ?
=VALUE(A5)-SUM(A10:A20)

Is your sum() returning an error? Then check whether any of the cells in the
sum range is having error. If all are positive try =SUMIF(A10:A20,">0")

or if both try (array entered)
=SUM(IF(ISNUMBER(A10:A20),A10:A20))

or

=SUMIF(A10:A20,">0")+SUMIF(A10:A20,"<0")