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; <, >, <, >=).
- 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.
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
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 1st 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 2nd 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,
=AVERAGEIFS(C4:C7, A4:A7,">=2017",B4:B7,">=1") and press 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 – 1st 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 – 2nd range to evaluated (contains all semester values)
“>=1” is criteria2 – we need both 1st &2nd semester, hence the “>=” logical operator.
- 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