We can use the **AVERAGEIF function** to calculate the average of numbers in a **range** that meet the specified **criteria**. Logical operators like **>, <, <>, =** and wildcards (***,?**) for partial matching are supported by the **criteria** of **AVERAGEIF**. The steps below will walk through the process.

*Figure 1- Final result of the AVERAGEIF function*

**Syntax**

**=AVERAGEIF(range,criteria,[average_range])**

**Range: This could be one or more cells, a named range, arrays, or references.**

- Criteria: This could be a number, an expression, cell reference, or text.

**Average_range**[optional]**:**These are the cells to get the average. If we omit it, the range is used.

**Formula**

**=AVERAGEIF($C$4:$C$13,">5000")**

**Setting up the Data**

- The
**Branches**of a store will be entered into**Column B** **Column C**will contain the**Sales**for each of the stores- The result will be returned in
**Column E**with the specified criteria in**Column D**

* Figure 2 – Setting up the Data*

**AVERAGEIF IF Sales is >5000**

- We will click on
**Cell E5** - We will insert the formula below into
**Cell E5**

**=AVERAGEIF($C$4:$C$13,">5000")** - We will press the
**enter**key

* Figure 3- AVERAGEIF if Sales is >5000*

In a similar fashion, we will enter the formula below into **Cell E4** and **Cell E6** to get the result

**Cell E4:**`=AVERAGEIF($C$4:$C$13,">0")`

- Cell E6:
`=AVERAGEIF($C$4:$C$13,"<5000")`

* Figure 4- Result of applying the AVERAGEIF function*

**Note**

- Any cell in a range that contains arguments like
**TRUE**or**FALSE**are ignored - Any empty cell within the range is ignored in
**range**and**average_range**with**AVERAGEIF Function** - The error value
**#DIV/0!**will be returned if there aren’t cells in the range that meet the criteria

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment