In Excel How to find sum of only positive numbers / figures in column? Sum of all numbers same color in sheet?

Question

Using Excel or OpenOffice I am curious how I find only the sum of only the positive numbers / figures in a column? Also I am wondering if there is a way to find the sum of all numbers of a certain colored text in a column and in the whole sheet. I know how to get the sum of numbers within a certain range of a column with using the =sum(A#:A#) but need to be able to get the sum of only the positive numbers as income as the negative amounts would not be deductible debt. Thanks.

I tried both without any luck. I have OpenOffice. It just gives me #Name? as if it does not understand it. I even tried hitting Shift Ctrl Enter after typing that in and copied and pasted it in, no luck. Surely there is something overlooked.

Answer 1

=SUMIF(A:A,">0")
will sum all positive numbers in column A.
=SUMIF(A5:A20,">0")
will sum all positive numbers from A5 to A20.

Answer 2

1

Answer 3

You could use the Conditional Sum Wizard in the Tools Menu. If it isn't in the drop-down menu you can install it from Add-ins.

The Wizard is fairly easy to use (with a bit of playing around when you first use it) Select >= and then from the next dropdown list (when you see it you'll know what I mean) select your lowest positive number from the column.

It should then add all number equal to your lowest positive number and all numbers higher than that.

I hope I have explained it well enough. If you would like to see an example email and I will send you one.

Answer 4

OK, here's an example.

Let's say you have 100 numbers in column A, and you want to sum only the positive ones. Click in a cell, then enter this formula:
=sum(if(A1:A100>0,A1:A100,0))

To sum only the negatives:
=sum(if(A1:A100>0,A1:A100,0))


For both formulas, you'll have to hit CTRL-SHIFT-ENTER once you type them, not just enter. These are called Array Formulas, and they require the special entry mechanism for that reason.


As far as summing the numbers of any other color... you'd have to write your own Excel macro to do this.

Answer 5

Place a second (empty) column next to the important one.
Assuming the original was column "A" then the formula in "B" is:

=IF ( A1>0, A1, 0)

Copy this formula down the column, and you will have a column with ONLY the positive numbers from column A. You can now sum this with SUM(B1:B1000)

Answer 6

First-class Online Store --> http://mall2.w3org.pw/xl3bh

Answer 7

Cool stuff I didn't know you could program IN excel!! Let alone for a good purpose, and in openoffice