Sum Values that are Greater Than Zero (SUMIF)

To sum values that are greater than zero, you can use the SUMIF function to specify a condition. In SUMIF, the criteria argument, allow you to write a condition where you can use the greater than operator and enter a zero (0). You can use the same range for testing conditions and calculating the sum.

sum-value-greater-than-zero

Or you can also use two different ranges, one for testing the condition and one to get the sum.

Steps to Write Formula: SUMIF Greater than Zero

You can use the below steps:

  1. First, in a cell enter the SUMIF function.
  2. After that, in the criteria_range refer to the Days Passed column.
  3. Next, in the criteria argument, use “>0”.
  4. Now, in the sum_range refer to the Quantity column.
  5. In the end, hit enter to get the result.
sumif-greater-than-zero
=SUMIF(B2:B11,">0",C2:C11)

As you can see, it only returns the sum for the values from the Quantity column, where you have a value above zero in the days passed column. You can see the same result on the status bar.

returns-the-sum-value

Using a Cell Reference to Specify Zero

While writing the formula, you can refer to a cell to specify the zero in it.

cell-reference-to-specify-zero
=SUMIF(B2:B11,">"&D1,C2:C11)

In the above formula, in the criteria, we have used an ampersand and then referred to cell D1 where we have the zero. This gives a dynamic formula where you can change the condition of the cell instead of editing it from the formula.

What If I Want to use SUMIFS Instead of SUMIF?

Yes, you can use it.

sumifs-greater-than-zero

In SUMIFS, you need to specify the sum_range first. After that, you need to specify the criteria range, and in the end, you need to enter the criteria to test.

=SUMIFS(C2:C11,B2:B11,">0")

Download Sample File