**What is the Excel AVERAGEIFS Function?**

The Excel **AVERAGEIFS** Function calculates the arithmetic mean (average) of the numbers in a range that meets one or more supplied criteria. The criteria in AVERAGEIFS supports wildcards (*,?) for partial matching and also logical operators; <, >, <, >=).

**Formula**

**=AVERAGEIFS(avg_rng,criteria1_range,criteria1,[criteria2_range],[criteria2], ...)**

**Explanation**

**avg_rng**– range of values to use to calculate an average. e.g.**C4:C7****criteria1_range1**– first range to evaluate. e.g.**A4:A7****criteria1**– criteria to use on range1.**criteria2_range**– second range to evaluate (optional)**criteria2**– The criteria to use on range2 (optional)

The criteria are supplied in **pairs** (range/criteria) and only the first pair is required. Every additional criterion requires an additional range/criteria pair and only a maximum of 127 range/criteria pairs are allowed.

The **AVERAGEIFS** Function returns the average (**numeric value**) of the cells that meet all criteria.

**Example**

How to use the Excel **AVERAGEIFS** Function

Let’s work with the scores of an algebra student for 4 semesters and make use of the **AVERAGEIFS** Function to find his mean scores for a different criterion.

The following steps should be taken;

**Open**the Excel spreadsheet.

* Figure 1. Sample data for AVERAGEIFS*

- We are to find his
**mean score for 2017**.**Click**on the cell that would contain our calculated score.

*Figure 2. Click on cell for mean score 2017*

**Insert**the**formula****=AVERAGEIFS(C4:C7,A4:A7,2017)**into that cell.

*Figure 3. Insert formula*

- Press
**Enter**. Our mean is**73.**You can test it with a calculator.

*Figure 4. Press ENTER*

*From the formula *`=AVERAGEIFS(C4:C7,A4:A7,2017)`

*;*

*C4:C7 **is where the AVERAGEIFS function gets the range of values to work on (i.e. all the scores)*

*A4:A7 **is our criteria1_range where we get the range of values to use as a “year”*

*2017 **is our criteria1. We want to calculate the mean score of all score gotten in 2017.*

- To calculate the
**mean score for 2018,**use the formula**=AVERAGEIFS(C4:C7,A4:A7,2018)**and press**Enter**.

*Figure 5. Mean score for 2018*

- To get the
**mean score for all 1****st****semesters,**use the formula**=AVERAGEIFS(C4:C7,B4:B7,1)**and press**Enter**. Here, ‘**B4:B7**’ is used as criteria_range because the values for the semester are in that range.

*Figure 6. Mean score for 1st semesters*

- For the
**mean score for all 2****nd****semesters,**use the formula**=AVERAGEIFS(C4:C7,B4:B7,2)**and press**Enter**.

*Figure 7. Mean score for 2nd semesters*

- Let’s switch things up a bit. We would use
**2 criteria**and try to calculate the**overall mean score for the 2 years.**

Use the formula,

and press **=AVERAGEIFS(C4:C7, A4:A7,">=2017",B4:B7,">=1") ****Enter.**

*Figure 8. Overall mean*

*C4:C7 **is the **avg_rng **as usual; as this is where the scores are stored.*

*A4:A7 **is the **criteria1_range1** – 1**st** range to be evaluated (contains all year values)*

*“>=2017” **is **criteria1** – we need all years from 2017 onwards, hence the “>=” logical operator.*

*B4:B7 **is the **criteria2_range** – 2**nd** range to evaluated (contains all semester values)*

*“>=1” **is **criteria2** – we need both 1**st** &2**nd** semester, hence the “>=” logical operator.*

**Note**

Common mistakes:

- Forgetting to use apostrophes in the criteria when you are using more than 1 criteria in your formula.
- Forgetting to pair a criterion with its criteria_range

## Leave a Comment