When working with large datasets in Excel, it is often necessary to sum values that meet a certain condition. One useful function for this purpose is the SUMIF function. In this article, we will explore how to use the SUMIF function to sum values greater than 0 in Excel. We will provide step-by-step instructions for three different approaches to using the SUMIF function.
Introduction to SUMIF Function in Excel
Microsoft Excel comes with a built-in function called SUMIF that enables you to add up values in a range that satisfies particular requirements. It is a versatile function that can be used to sum data based on a single condition or multiple conditions.
The syntax of the SUMIF function:
The arguments are as follows:
| Arguments | Necessity | Value |
|---|---|---|
| range | Required | The range of cells that you want to search by condition. The range of cells must be numbers, names, arrays, or references that have numbers. Blank and text values are ignored. |
| criteria | Required | The criteria are in the form of a number, expression, cell reference, text, or a function that defines which cells will be added. |
| sum_range | Optional | For the actual cells to add, we want to add cells other than those specified in the range argument. If the sum_range argument is removed, Excel adds the cells that are specified in the range argument. |
When analyzing large datasets, the SUMIF function can be a very useful tool and help you save a lot of time and effort.
How to Use Excel SUMIF to Sum Values Greater Than 0: 3 Handy Examples
In this article, we will explore three cases to use the SUMIF function to sum values greater than 0 in Excel. Case 1 is the simplest form of use of SUMIF function while the other two will show more advanced and dynamic forms of uses of the function. In the later part of the article we also discussed some of the issues that may arise while working with SUMIF function and how you can resolve them. Hope you will stay with us till the end of the article.
1. Sum Values Greater Than 0 Typing Criteria Value Inside Double Quotes in Excel Formula
While using the SUMIF function to sum values greater than 0, you have the option to include the condition inside the formula.
Suppose you have some products that generated significant revenues over a period of time, but others didn’t do quite as well as per your expectations. You want to calculate the sum of those products that generated positive revenue and exclude the negative ones. The SUMIF function gives you wonderful functionality in this case.
- In the image, as you can see, in cell C15, we inserted this formula.
=SUMIF(C5:C14,">0")In the ‘range’ argument, we put the range C5:C14 to calculate the sum, and in the ‘criteria’ argument, the condition “>0” is inserted with inverted commas (“”).
- Press Enter, and you will get the sum value, which only summed values greater than 0.
Read More: How to Use Excel SUMIF with Greater Than Criterion
2. Working with SUMIF Function When Criteria Range Is Different
Suppose, your sum range and criteria range isn’t the same. Then, how do you modify your formula to calculate the sum for values greater than 0? In this section, we will discuss that.
Let’s look at the example we are using in this section. We have some sales data for some products (D5:D14) and the number of products left in the inventory (C5:C14). Now, we want to calculate Total sales but only for those items that are still left in the inventory. That means, our criteria range will be cells C5:C14 and as specified before the criteria is “>0”.
- In the image, as you can see, in cell D15, we inserted this formula.
=SUMIF(C5:C14,">0",D5:D14)Here’s a Breakdown of the Formula:
- In the ‘range’ argument, we put the range C5:C14 to be evaluated based on a specific condition. and in the ‘criteria’ argument, the condition “>0” is inserted with inverted commas (“”).
- The third argument is the range D5:D14, which contains the values to be summed based on the condition specified in the first argument.
So, the formula will sum all the values in D5:D14, but only if the corresponding value in C5:C14 is greater than 0. If a value in the range C5:C14 is not greater than 0, it will be excluded from the sum.
- Press Enter, and you will get the sum value.
Read More: How to Sum If Cell Contains Number in Excel
3. Putting Cell Reference of the Criteria Value Inside Formula
If you want a dynamic formula, you can put a cell reference in the formula instead of the value. Let’s see how adding cell references can help us in this case.
For the purpose of creating a dynamic formula, we will create another table where we will insert the criteria value. Now, since we want to calculate Total sales but only for those items that are still left in the inventory, our criteria range will be cells C5:C14 and as specified before the criteria is “>0”. But we will use a cell reference in this context.
- In the image, as you can see, in cell C18, we inserted this formula.
=SUMIF(C5:C14,">"&C17,D5:D14)
Here’s a Breakdown of the Formula:
- We specify the C5:C14 range to be assessed depending on a certain condition in the ‘range’ parameter.
- The second argument is the criteria “>”&C17. The “&” symbol is a concatenation operator that combines the “>” symbol with the value in cell C17. This specifies that the function will include only values greater than the value in cell C17.
- The numbers to be summed up according to the condition stated in the first argument are included in the range D5:D14, which is the third argument.
So, the formula will sum all the values in D5:D14, but only if the corresponding value in C5:C14 is greater than the value in cell C17. Now, if you want to get the sum values greater than any other value except 0, you just have to change the value in cell C17.
- Press Enter, and you will get the sum value.
Note:
- To add values that are less than 0, just change the > operator (“greater than” operator) to < (“less than” operator).
- To add values that are not equal to 0, just use <> operator inside the formula.
Read More: Sum If Greater Than and Less Than Cell Value in Excel
How to Use SUMIFS Function to Sum Values Greater than 0 in Excel
The SUMIF function does a wonderful job when it comes to summing a range with a condition but when you need to apply multiple conditions, SUMIFS is the function you want to go for.
Suppose, you want to calculate Total sales but only for those items that are still left in the inventory and products that were delivered before January 20, 2023.
- So, in the image, as you can see, in cell E15, we inserted this formula.
=SUMIFS(E5:E14,C5:C14,">0",D5:D14,"<20-Jan-23")Here’s a Breakdown of the Formula:
• The first argument in the function refers to the range E5:E14, containing the values that need to be summed based on the criteria specified in the second and third arguments.
• The second argument refers to the range C5:C14, which is evaluated based on the criteria “>0”. This condition ensures that only values greater than 0 are included in the sum calculation.
• The third argument refers to the range D5:D14, which is evaluated based on the criteria “<20-Jan-23”. This condition specifies that the function includes only values that are less than the date “20-Jan-23” in the sum.
So, the formula will sum all the values in E5:E14 that meet both conditions: they are greater than 0 and less than the date “20-Jan-23”.
• Press Enter, and you will get the sum value.
The main difference between the SUMIF and SUMIFS functions in Excel is the number of criteria that can be used to evaluate the sum range. You can specify up to 127 pairs of criteria ranges and criteria in the SUMIFS function.
Frequently Asked Questions
1. Can I use the SUMIF function to calculate values greater than 0 in a range of cells that contains errors or empty cells?
f you want to exclude error values from the calculation, you can use the SUMIF function in combination with the IFERROR function to replace error values with a 0. Usually, the SUMIF function ignores empty cells.
2. Can I use the SUMIF function with non-numeric values?
Yes, you can use the SUMIF function with non-numeric values. The function can sum up any values that meet the specified criteria, regardless of whether they are numeric or non-numeric. However, if you try to use the function with non-numeric values that cannot be converted to a number, it will return an error.
3. Can I use wildcards with the SUMIF function?
Yes, you can use wildcards such as asterisks (*) and question marks (?) with the SUMIF function to match criteria based on partial text matches. The asterisk represents any number of characters, and the question mark represents a single character.
Download Practice Workbook
Download this Excel workbook file to practice with us.
Conclusion
The SUMIF function is a powerful tool for quickly summing data based on specific criteria. By using the SUMIF function in combination with logical operators and cell references, you can easily sum values greater than 0 in your Excel spreadsheets. Whether you prefer to use a simple formula or a more complex approach, the SUMIF function can save you time and effort when working with large datasets. With the knowledge and techniques presented in this article, you can more effectively analyze and summarize your data in Excel.
Related Articles
- How to Sum If Cell Contains Number and Text in Excel
- How to Use SUMIF to SUM Less Than 0 in Excel
- How to Use 3D SUMIF for Multiple Worksheets in Excel
- How to Use Excel SUMIF Function Based on Cell Color
<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
