Adding up all the positive numbers in Excel

Question

Is there a way to add up all of the positive numbers in a row/column but ignoring all of the negative numbers? Like SUM(), except that it ignores negative numbers. Would I have to use VBA? If so, how would I do it in VBA?

If it can't be done in Excel, can it be done in OpenOffice Calc?

Answer 1

Use SUMIF. YTo sum all the positive numbers in Column A:

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

Same function exists in Excel and Calc

Answer 2

=SUMIF(A1:A99,">0")

Answer 3

Sure like this:

=SUMIF(B1:B50,">0")

This will add all positive numbers in cells B1 to B50.

Answer 4

=SUMIF(A1:A4,">=0")

Answer 5

SUMIF() will do the trick:

 =SUMIF(A1:A100,">0")

You could also use an array formula, but that's more complicated and has no benefit over SUMIF()

Answer 6

Say your data are in column A.

1) Type "=if(A1>0, A1, 0)" in B1

2) Fill down to the end of the data

3) Highlight data in column B

Sum appears in taskbar at bottom.

Answer 7

Get auxiliary row/colums, in another tab if necessary them fill them with =if(A5>0;A5;0). That will turn negatives to zero. Then sum those.