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.

**Table of Contents**hide

## 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**:

**SUMIF**

**(range, criteria, [sum_range])**

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
, we inserted this formula.*C15*

`=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

**is inserted with inverted commas (**

*“>0”***“”**).

- Press
**Enter**, and you will get the sum value, which only summed values greater than 0.

**Read More: How to Sum If Cell Contains Number and Text in Excel**

### 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 (

**). 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**

*C5:C14***.**

*“>0”*- In the image, as you can see, in cell
, we inserted this formula.*D15*

`=SUMIF(C5:C14,">0",D5:D14)`

**Here’s a Breakdown of the Formula:**

- In the ‘range’ argument, we put the range
to be evaluated based on a specific condition. and in the ‘criteria’ argument, the condition*C5:C14*is inserted with inverted commas (*“>0”***“”**). - The third argument is the range
, which contains the values to be summed based on the condition specified in the first argument.*D5:D14*

So, the formula will sum all the values in ** D5:D14**, but only if the corresponding value in

**is greater than 0. If a value in the range**

*C5:C14***is not greater than 0, it will be excluded from the sum.**

*C5:C14*- 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

**. But we will use a cell reference in this context.**

*“>0”*- In the image, as you can see, in cell
, we inserted this formula.*C18*

`=SUMIF(C5:C14,">"&C17,D5:D14)`

**Here’s a Breakdown of the Formula:**

- We specify the
range to be assessed depending on a certain condition in the ‘range’ parameter.*C5:C14* - The second argument is the criteria
. The “*“>”&C17***&**” symbol is a concatenation operator that combines the**“>”**symbol with the value in cell. This specifies that the function will include only values greater than the value in cell*C17*.*C17* - The numbers to be summed up according to the condition stated in the first argument are included in the range
, which is the third argument.*D5:D14*

So, the formula will sum all the values in ** D5:D14**, but only if the corresponding value in

**is greater than the value in cell**

*C5:C14***. 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***.**

*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: How to Use Excel SUMIF with Greater Than Criterion**

## 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
, we inserted this formula.*E15*

`=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.

**Read More: How to Use SUMIF to SUM Less Than 0 in Excel**

**Similar Readings**

**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**

## 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 Use Excel SUMIF with Blank Cells****How to Use SUMIF Function to Sum Not Blank Cells in Excel****Excel SUMIF Not Working****[Fixed!] Excel SUMIF with Wildcard Not Working****Sum If Greater Than and Less Than Cell Value in Excel****Excel SUMIF Function for Not Equal Criteria****Excel SUMIFS with Not Equal to Text Criteria**