Picture this: you’ve got a spreadsheet congested with data, and all you need is a straightforward way to extract the positive numbers. The struggle is real, and that’s where the SUMIF function comes to your rescue.
Whether you’re a financial whiz crunching numbers or a project manager tracking budgets, separating positive values is a common task.
For all those users who are looking for an easy solution, in this post, we will guide you through the exact ways of using the SUMIF function to calculate values that are greater than zero.
So, let’s get into it.
SUMIF Function in Excel – At a Glance
The SUMIF function in Excel is used to add values based on a specific condition or criteria. Whether you are managing budgets, analyzing sales data, or tracking project expenses, SUMIF is always a handy function because it provides a simple yet effective way to calculate sums selectively.
That said, it provides a way to selectively analyze and summarize the data.
For instance, you can use SUMIF in financial spreadsheets to calculate the total expenses for a certain category or department.
Let’s say you have a column (D) with salespeople’s names and another column (E) with their corresponding salaries. If you want to know the monthly salary for a specific salesperson, let’s say Sara, then type the following formula in an empty cell.
=SUMIF(D:D, “Sara”, E:E)
This formula sums all the monthly salaries in column E where the corresponding name in column D is Sara.
The basic syntax of the SUMIF function includes three main components: the range of cells you want to evaluate, the criteria to satisfy, and the range of cells containing the values you want to sum (More on that later).
How to Use Excel SUMIF to Sum Values Greater than 0
As the name suggests, the SUMIF function combines the functionality of two operations: SUM and IF.
The SUM part calculates the sum of values, and the IF part introduces a conditional aspect to the calculation. It means that the sum will only include values that meet a specific condition.
So, when you use the SUMIF function, you provide a range of cells, a condition that is greater than zero, and a range of corresponding values to be summed.
Then, the formula adds up only those values that satisfy the specified condition.
The syntax of the SUMIF function is as follows:
=SUMIF(range, criteria, [sum_range])
The following table resembles the details of the arguments used in this formula.
| Argument | Description |
|---|---|
| range | It is the range of cells to which you want to apply the criteria. |
| criteria | It is the condition you want to apply to the range. |
| [sum_range] | This argument is optional. It specifies the actual cells to sum that fulfill the given criteria. |
Below, I’ve explained how to use SUMIF to sum values that are greater than 0:
1. Sum Values Greater than Zero with a Specified Cell Range
Let’s assume you have the following dataset, where column A contains your product list from cell A2:A6, and column B contains the values of those products from cell B2:B6.
Now, use the =SUMIF() function to add the numbers in column B where the corresponding value is greater than 0.
In cell B9 or any other empty cell where you want to display the result, enter the following formula:
=SUMIF(B2:B6, “>0”)
Here, B2:B6 indicates the range of values you want to evaluate. In this case, it’s the values in column B from row 2 to row 6, and “>0” specifies that only values greater than zero should be included in the sum.
In this example, the result will be 5 + 8 = 13, as only the values corresponding to Item 1 and Item 3 are larger than 0.
2. Add Values Greater than Zero with a Dynamic Cell Range
Suppose you have an Excel spreadsheet with three columns: Column A contains your products from cell A2:A6, Column B consists of the quantity of those products from cell B2:B6, and Column C holds the number of items.
Let’s use the =SUMIF() function to add up the values in column C, where the corresponding value in column B is greater than zero.
In an empty cell, type the following formula and press Enter:
=SUMIF(B2:B6, “>0”, C2:C6)
Here, B2:B6 is the range of cells that the formula evaluates based on the given criteria. “>0” is the criteria that specify we want to sum values if greater than 0, and C2:C6 is the range of cells that contains the values we want to sum if the criteria are met.
In this example, the formula will sum the values in column C where the corresponding value in column B is greater than 0. The result should be the sum of 100, 75, and 120, which is 295.
Modify the dataset or criteria based on your specific needs.
3. Sum Values Greater than 0 with Cell Reference
Assuming that you have a worksheet in Excel with three columns, where Column A is a fruit list from cell A2:A6, Column B consists of the quantity of those fruits from cell B2:B6, and Column C holds the revenue from each fruit.
In an empty cell (B8), enter the value 0. It will be the reference cell, presenting the condition for the SUMIF formula.
Now, we will use the =SUMIF() function to add up the revenues for the available fruits, i.e., whose quantity is greater than 0.
In an empty cell, enter the following formula:
=SUMIF(B2:B6, “>”&B8, C2:C6)
Here, B2:B6 indicates the range of cells the formula checks based on the criteria. “>”&B8 verifies if the value in the specified range of cells is greater than the B8 cell value, which in this case is 0, and C2:C6 is the range of cells that contains the values we’ll add up if it fulfills the criteria.
In the above example, the formula will add up the revenue from Column C for the fruits Apple, Banana, and Kiwi because their quantity is higher than 0.
In case you want to add up values less than or equal to zero, simply replace the relational operator with “<” or “=” in the described formula.
Can You Use SUMIFS Instead of SUMIF to Sum Up if Greater than Zero (0)?
Yes, you can use the SUMIFS function instead of SUMIF to sum up the values greater than zero (0). In Excel, both SUMIF and SUMIFS are used to sum values based on certain conditions, but they differ in their capabilities. SUMIF is designed for a single condition, while SUMIFS allows for multiple criteria.
In fact, you can insert up to 127 conditions in a single formula.
However, suppose you want to use the SUMIF function with multiple conditions. In that case, you can still do it by adding different SUMIF functions together, as you can combine two formulas in Excel.
But we wouldn’t recommend it because it’ll make the formula complex and hard to read. So, use SUMIFS when you need to check for multiple criteria.
To sum values greater than zero using SUMIFS, specify the range to sum, set the first criterion as greater than 0, and leave additional criteria blank if not needed.
This way, SUMIFS considers only the cells meeting the specified condition (values greater than 0) and sums them up.
The syntax of the SUMIFS function is as follows:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
| Argument | Description |
|---|---|
| sum_range | It is the range of cells that you want to sum. |
| criteria_range1 | It is the first range where you want to apply a condition. |
| criteria1 | It is the condition to be met in ‘criteria_range1’. |
| [criteria_range2, criteria2], ... | It specifies additional ranges and criteria. You can have up to 127 pairs of criteria_range and criteria. |
Let’s create a simple Excel dataset and use the SUMIFS formula to sum values greater than 0 based on multiple criteria.
In the following example, we will create a dataset representing sales data for a company with five columns, where column A contains dates, column B has product names, column C holds the region list, column D contains the sales number, and column E describes the quantity, expanding from cell A2 to E11.
Now, let’s use the =SUMIFS() function to sum the ‘Sales’ column based on the criteria that the ‘Sales’ value should be larger than 0 and the ‘Region’ is ‘North.’
In an empty cell, enter the following formula:
=SUMIFS(D2:D11, C2:C11, “North”, D2:D11, “>”&0)
This formula adds up the D2:D11 cells (Sales) where the C2:C11 (Region) is ‘North,’ and the ‘Sales’ value is greater than zero.
The result will be the sum of sales for the specified criteria, and it should be 500 + 300 + 400 = 1200.
Final Thoughts
As you can see, when managing financial data, tracking inventory, or analyzing project expenses, the SUMIF function is an indispensable feature. It allows you to extract meaningful information with precision and ease.
