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

*
Figure 1- Final result of the AVERAGEIFS function*

**Syntax**

**=AVERAGEIFS(avg_rng, range1, criteria1, [range2], [criteria2], ...)**

**avg_rng:**This is the range whose average we want. This range can either be a subset of range1 or range1.**range1:**The first range to evaluate.**criteria1:**This is the boundary where the**AVERAGEIFS function**can evaluate range1**range2 [optional]:**The second range to evaluate.**criteria2 [optional]:**The criteria to use on range2.

**Formula**

**=AVERAGEIFS($C$4:$C$13,$C$4:$C$13,">1000",$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*

**AVERAGEIFS if Sales is >1000 and <5000**

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

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

* Figure 3- AVERAGEIFS if Sales is >1000 and <5000*

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

**Cell E5:**`=AVERAGEIFS($C$4:$C$13,$C$4:$C$13,">3000",$C$4:$C$13,"<5000")`

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

* Figure 4- Result of applying the AVERAGEIFS function*

**Note**

**AVERAGEIFS**allows up to 127 range/criteria pairs apart from the first compulsory range and criteria- Every added range must have similar number of rows and columns as the
**avg_range** **Criteria**should be enclosed in**double quotes**- Wildcards like question mark matches any one character and asterisk matches any order of characters.
- 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