If you have a dataset that contains both negative and non-negative values and want to **calculate the average** of values that are greater than zero then Excel can be helpful for you. In this article, you will get to know how you can **average values greater than zero** in Excel.

**Table of Contents**hide

## Download Practice Workbook

## 4 Easy Ways to Average Values Greater Than Zero in Excel

Here, I have taken the following dataset that contains the **Month** and the **Profit** columns. The **Profit **column contains both **positive** and **negative** values. Here, the **negative** value means **loss**, and **0** means a **breakeven point**. I will show you how you can **average values greater than zero** in Excel by using this dataset. I will explain **4 **easy and effective ways.

### 1. Using AVERAGEIF Function to Average Values Greater Than Zero in Excel

In this first method, I will explain how you can **average values greater than zero** by using **the AVERAGEIF function**. Suppose you have the following dataset that contains both **positive** and **negative**** Profits**. Here, **negative profits** mean **loss**. And, you want to calculate the **Average Profit** which means the **average of the values that are greater than zero**.

Let me show you how you can do it.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Average Profit**. Here, I selected cell**C12**. - Secondly, in cell
**C12**write the following formula.

`=AVERAGEIF(C5:C10,">0")`

Here, in the **AVERAGEIF** function, I selected **C5:C10** as the **range** and **“>0”** as the **criteria**. The formula will return the average of the values from the **range** that match the **criteria**.

- Thirdly, press
**ENTER**and you will get your**Average Profit**.

**Read More:** **How to Average Negative and Positive Numbers in Excel**

### 2. Applying AVERAGEIFS Function in Excel

Here, I will explain how you can **average values greater than zero** by applying **the AVERAGEIFS function**. Let’s see the steps.

**Steps:**

- Firstly select the cell where you want to
**average values greater than zero**. Here, I selected cell**C12**. - Secondly, in cell
**C12**write the following formula.

`=AVERAGEIFS(C5:C10,C5:C10,">0")`

Here, in the **AVERAGEIFS** function, I selected cell range **C5:C10** as **average_range**. Then, I selected cell range **C5:C10** as** criteria_range1** and **“>0”** as** criteria1**. Now, the formula will return the average of the values from the **average_range** that match the **criteria1**.

- Finally, press
**ENTER**to get the result.

**Read More:** **How to Calculate Average True Range in Excel (with Easy Steps)**

### 3. Employing AVERAGE and IF Functions

In this method, I will show you how you can **average values greater than zero** by employing **the** **AVERAGE function** and **the IF Function**. Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Average Profit.** - Secondly, in that selected cell write the following formula.

`=AVERAGE(IF(C5:C10>0,C5:C10,""))`

**Formula Breakdown**

**IF(C5:C10>0,C5:C10,””) —->**Here, the**IF**function will check if**C5:C10>0**. If the**logical_test**is**True**then the formula will return**C5:C10**. Otherwise, it will return a**blank**.**Output: {1000;500;””;””;700;””}**

**AVERAGE(IF(C5:C10>0,C5:C10,””)) —->**turns into**AVERAGE({1000;500;””;””;700;””})****—->**Now, the**AVERAGE**function will return the average of the values.**Output: 733.33333**

- Finally, press
**ENTER**and you will get the**Average Profit**.

**Read More:** **[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)**

**Similar Readings**

**How to Ignore #N/A Error When Getting Average in Excel****Calculate Average Numbers in Excel (9 Handy Methods)****How to Average Filtered Data in Excel (2 Easy Methods)****Fix Divide by Zero Error for Average Calculation in Excel****How to Calculate Average from Different Sheets in Excel**

### 4. Use of SUMIF and COUNTIF Functions to Average Values Greater Than Zero

In this method, I will explain how you can use the **SUMIF** and **COUNTIF** functions to **average** **values greater than zero** in Excel. Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Average Profit**. Here, I selected cell**C12**. - Secondly, in cell
**C12**write the following formula.

`=SUMIF(C5:C10,">0",C5:C10)/COUNTIF(C5:C10,">0")`

**Formula Breakdown**

**SUMIF(C5:C10,”>0″,C5:C10) —->**Here, the**SUMIF**function will return the**summation**of the values that match the**criteria**.**Output: 2200**

**COUNTIF(C5:C10,”>0″) —->**Here, the**COUNTIF**function will count the number of cells that match the**criteria**.**Output: 3**

**SUMIF(C5:C10,”>0″,C5:C10)/COUNTIF(C5:C10,”>0″) —->**turns into**2200/3 —->**Now, the formula will**divide 2200**by**3**.**Output: 733.33333**

- Finally, press
**ENTER**to get the**Average Profit**.

**Read More:** **How to Calculate Sum & Average with Excel Formula**

## How to Average a Column Based on Criteria of Another Column in Excel

In this section, I will show you how to **average a column** based on the **criteria of another column** in Excel. Here, I have taken the following dataset to explain this example. This dataset contains the **Month**, **Sales**, and **Profit **columns. I will show you how you can calculate the **Average Sales** if the **Profit **is** greater than zero**.

Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Average Sales**. Here, I selected cell**C15**. - Secondly, in cell
**C15**write the following formula.

`=AVERAGEIF(D5:D10,">0",C5:C10)`

Here, in the **AVERAGEIF **function, I selected cell range **D5:D10** as the **range** and **“>0”** as the **criteria**. Then, I selected the cell range **C5:C10** as **average_range**. Now, the formula will return the average of the values from **average_range** that match the **criteria**.

- After that, press
**ENTER**to get the**Average Sales**.

**Read More:** **How to Calculate Monthly Average from Daily Data in Excel**

## Things to Remember

- You should keep in mind that If the
**AVERAGEIF**function fails to meet the**criteria**then it will return**#DIV/0!**error.

## Practice Section

Here, I have provided a practice sheet for you to practice how you can get **average values greater than zero** in Excel.

## Conclusion

To conclude, I tried to cover how you can **average values greater than zero** in Excel. Here, I explained **4 **easy ways of doing it. I hope this article was helpful for you. Lastly, if you have any questions feel free to let me know in the comment section below.

## Related Articles

**How to Find Average of Specific Cells in Excel (3 Handy Ways)****Calculate Average of Averages in Excel (with Easy Steps)****How to Calculate Average Only for Cells with Values in Excel****Calculate Average of Multiple Ranges in Excel (3 Methods)****How to Calculate Average of Multiple Columns in Excel (6 Methods)**