Trying to add a column of numbers, but ignoring negatives

**Lord Waste** · 04-07-2009, 06:21 AM

Hi,

I'm trying to SUM a column, but it has a negative number in it. I want to sum the column but ignore the negative number.

I.E.
5
46
12
-15
3

so the total would be 66.

How is this possible or would i have to use another formula instead of SUM.

Cheers,

John

**Back2Basics** · 04-07-2009, 06:36 AM

If you add a helper column you can take out the negatives from that;
=IF($A1>0, A1,0)

then sum that column

**Lord Waste** · 04-07-2009, 06:39 AM

There is also other numbers being added weekly, so the total needs to update it's self as the numbers get added.

Ideally would like to do this as one column with out having to deal with anymore, as I already have 30 columns of information to deal with.

**martindwilson** · 04-07-2009, 06:40 AM

=sumif(a1:a10,">0")
or whole column

=sumif(A:A,">0")

**Lord Waste** · 04-07-2009, 06:42 AM

Originally Posted by martindwilson

=sumif(a1:a10,">0")
or whole column

=sumif(A:A,">0")

Thank you martin.

**Back2Basics** · 04-07-2009, 06:43 AM

For the sum formula you can get a dynamic range.

try this link: http://www.ozgrid.com/Excel/DynamicRanges.htm

you can use this so that it will sum all of the values, up to the last cell that has a number in it.

I think there is a similar thing you can do with the formula, but not sure howto do it myself. It will have offset in there somewhere i guess, maybe try startinga new thread for this question... something along the lines of "creating a dynamic formula" hope this helps