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.
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:
- First, in a cell enter the SUMIF function.
- After that, in the criteria_range refer to the Days Passed column.
- Next, in the criteria argument, use “>0”.
- Now, in the sum_range refer to the Quantity column.
- In the end, hit enter to get the result.
=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.
Using a Cell Reference to Specify Zero
While writing the formula, you can refer to a cell to specify the zero in it.
=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.
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")
Get the Excel File
DownloadMore
- Sum Greater than Values using SUMIF
- Sum Not Equal Values (SUMIFS) in Excel
- SUMIF / SUMIFS with an OR Logic in Excel
- SUMIF with Wildcard Characters in Excel
- SUMIFS Date Range (Sum Values Between Two Dates Array)
- Combine VLOOKUP with SUMIF
- Sum IF Cell Contains a Specific Text (SUMIF Partial Text)
- Sum Values Based on Year (SUMIF Year)
- SUMIF By Date (Sum Values Based on a Date)
- SUMIF Non-Blank (Sum Values for Non-Empty Cells)
- Use SUMIF to Sum Blank Values or Empty Cells
- Sum Values Less Than a Particular Value (SUMIF Less Than)
- Sum Values Based on the Month (SUMIF)
⇠ Back to Excel Formula List