Excel is an excellent tool for a wide range of calculations, among which summation is maybe the easiest. However, summing only positive numbers may be a challenge. Not if you follow this tutorial!
In this tutorial, we will learn how to sum positive numbers in Excel using the SUMIF function.
Table of Contents
Sum Positive Numbers Using the SUMIF Function
The SUMIF function enables you to sum cells or values under some criterion. The function takes three arguments:
=SUMIF(criteria_range, criteria, sum_range)
Let me explain all the arguments:
- criteria_range: This is the range of cells or a list of values that you want to apply your criteria.
- criteria: This is where you specify the criteria. For example, if you only want positive numbers, you need to specify “>0”. Similarly, if you want negative numbers, write “<0”. You need to surround these criteria with double quotes.
- sum_range: This is the range of cells or list of values you want to sum up. Usually, the criteria range and sum range are the same, so you can omit this argument.
Now let’s use the SUMIF formula to sum up values in the cells B1:B6. Since both the sum and criteria ranges are the same, I will omit the third argument.
To sum up positive numbers, write the following formula in a new cell:
=SUMIF(B1:B6, ">0")
This formula will check each cell for positivity. If positive, then it will be included in the sum. As can be seen from the image, the positive cells (painted green) are summed up: 10 + 20 + 5 + 2 = 37. The negative numbers are not included in the sum.
Also specifying the sum range (the third argument of the function) will yield the same result:
=SUMIF(B1:B6, ">0", B1:B6)
You can use either approach. As you can see, the first and third arguments are the same. These arguments can be provided differently in more complicated scenarios.
If you want to sum up negative numbers, you simply change the criteria from “>0” to “<0”:
=SUMIF(B1:B6, "<0")
This time, the cells are checked for negativity, and only negative values are included in the sum: -10 + -8 = -18.
Otherwise, change the negative numbers to positive first before you sum them up. Then get the sum across multiple sheets if needed.
In this tutorial, we learned the SUMIF function and how to use it to sum up positive and negative numbers. Hope it was useful :)