Using Microsoft Excel, which function do I use to represent the following:?

Question

I am creating a spreadsheet, and I would like to make it so that all values that are larger than 0 are added together, but any cell that has a value of 0 is left alone. I can add them using SUM(A1:B1), but when I paste that formula, the blank cells are inserted with 0. Is there any way to work around this?

Answer 1

If you want to show a blank when there is nothing to add, then this should work:

=IF(SUM(A1:B1)>0, SUM(A1:B1),"")

If you only want to add a number if it's greater than 0, then the SUMIF formula will work:

=SUMIF(A1:B1,">0")
This will ignore any cells that have 0 (which wouldn't change the total anyway), or which are negative.

I hope this helps. If not, pls. add detail or e-mail.
____
By the way, if you just want to hide 0s, the other way to do that is with formatting:
- Format - Cells - Number tab
- pick Custom, and in the Type box at right, enter 0;-0;
the first 0 is for positive numbers, the second is for negative numbers, and the fact that there is nothing after the second semicolon means any 0s won't be shown at all

Answer 2

if you want the formulas not to have zeros, just do an if formula.

For eg.
=if(sum(A1:B1)>0,sum(A1:B1),"")

Two inverted commas in the formula("") will keep your cell blank if the addition results in a value less than or equal to zero.

Answer 3

Sumif. =sumif(A1:A20),>0