### Explanation of the AVERAGEIFS function

The AVERAGEIFS function allows you to evaluate for multiple criteria with the following argument structure:

- average_range
- criteria_range1, criteria1
- criteria_range2, criteria2
**Note:**You can input up to a max of 127 [criteria_range, criteria]

In the image below I purposely typed a few commas to force the argument description to show multiple criteria.

**AVERAGEIF**–> evaluates for only 1 criteria- AVERAGEIFS –> evaluates for multiple criteria.
**Note:**You can still use the AVERAGEIFS function if you only have 1 criteria. In this way it will act like an AVERAGEIF function.

#### Example data used to explain how the AVERAGEIFS function works

- 3 different colors
- each color has 3 different sizes
- 2 locations (color coded to easily distinguish them apart)
- Quantity sold at each of the 2 locations

#### 1. average_range (1st argument)

The **average_range** is what the AVERAGEIFS function will use to average the numbers whenever it finds a match for your **criteria** in the **critera_ranges**.

#### 2. criteria_range1, criteria1 (2nd and 3rd arguments)

The AVERAGEIFS function will use the **criteria_ranges **to find a match against your **criteria **for each pair of [criteria_range, criteria] that you provide.

**Note:** You can input up to 127 pairs of [criteria_range,criteria].

#### How does the AVERAGEIFS function work?

You should use the AVERAGEIFS function if you need the average of a set of numbers when multiple **criteria **are true.

When the function finds a match for each **criteria** in the corresponding **criteria_ranges **(which is evaluated for each pair of **[criteria_range,criteria]**), it will average the corresponding numbers from the **average_range** into the final result.

For each row within your data set:

- If at least 1 of the
**criteria**does not match the value from its**criteria_range**–> the corresponding value from the**average_range**is ignored - If each
**criteria**matches the value from its**criteria_range**–> the corresponding value from the**average_range**is included in the average

Keep in mind the AVERAGEIFS function syntax when reading the Formulas shown below… **=AVERAGEIFS(average_range,criteria_range1,criteria1,**….<all the way to>….**,criteria_range127,criteria127)**

#### Explanation of results

- Blue shirts sold in location A = (8 + 19 + 6) / 3 = 33 / 3 = 11
- White and Large shirts sold in location B = 19 / 1 = 19
- Black and Medium shirts sold = (13 + 2) / 2 = 15 / 2 = 7.5
- All shirts sold in location B = (18 + 2 + 15 + 9 + 17 + 19 + 12 + 2 + 1) / 9 = 95 / 9 = 10.56

#### Go back to **Excel Tutorials**.