AVERAGEIFS Function in Microsoft Excel
Part 1: Introduction
Definition: The AVERAGEIFS function in Microsoft Excel returns the average (arithmetic mean) of all cells that meet multiple criteria.
Purpose: This function is used when you want to compute the average of a range of cells that satisfy multiple specified conditions.
Syntax & Arguments:
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) Arguments:
average_range: Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.criteria_range1, criteria_range2, …: Criteria_range1 is required, subsequent criteria_ranges are optional. 1 to 127 ranges in which to evaluate the associated criteria.criteria1, criteria2, ...: Criteria 1 is required. Subsequent criteria are optional. 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged.
Return value: The function returns the average of the cells that meet all the specified criteria.
Remarks: If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! Error value. You can use the wildcard characters, question mark (?), and asterisk (*) in the criteria.
Part 2: Examples
Let’s look at some examples of how to use the AVERAGEIFS function in a business context.
Example 1
Purpose: To find the average product sales within a specific price range.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Sales | Formula | Result |
| 2 | Product A | 100 | ||
| 3 | Product B | 200 | ||
| 4 | Product C | 300 | ||
| 5 | Average | =AVERAGEIFS(B2:B4, B2:B4, ">150", B2:B4, "<250") | 200 |
Explanation: This formula calculates the average sales of products that have sales between 150 and 250. Only Product B meets the criteria in this case, so the average sales is 200.
Example 2
Purpose: To find the average product sales from a specific region within a certain price range.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Formula | Result |
| 2 | Product A | 100 | Region 1 | ||
| 3 | Product B | 200 | Region 2 | ||
| 4 | Product C | 300 | Region 1 | ||
| 5 | Average | =AVERAGEIFS(B2:B4, B2:B4, ">150", C2:C4, "Region 1") | 300 |
Explanation: This formula calculates the average sales of products from Region 1 that have sales over 150. Only Product C meets the criteria in this case, so the average sales is 300.
Example 3
Purpose: To find the average sales of a product from a specific region and a specific category.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Category | Formula | Result |
| 2 | Product A | 100 | Region 1 | Cat 1 | ||
| 3 | Product B | 200 | Region 2 | Cat 2 | ||
| 4 | Product C | 300 | Region 1 | Cat 1 | ||
| 5 | Average | =AVERAGEIFS(B2:B4, C2:C4, "Region 1", D2:D4, "Cat 1") | 200 |
Explanation: This formula calculates the average sales of products from Region 1 and Category 1. Products A and C meet the criteria in this case, so the average sales is 200.
Example 4
Purpose: To find the average product sales from a specific region, a specific category, and within a certain price range.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Category | Formula | Result |
| 2 | Product A | 100 | Region 1 | Cat 1 | ||
| 3 | Product B | 200 | Region 2 | Cat 2 | ||
| 4 | Product C | 300 | Region 1 | Cat 1 | ||
| 5 | Average | =AVERAGEIFS(B2:B4, B2:B4, ">150", C2:C4, "Region 1", D2:D4, "Cat 1") | 300 |
Explanation: This formula calculates the average sales of products from Region 1 and Category 1, with sales over 150. Only Product C meets the criteria in this case, so the average sales is 300.
Example 5
Purpose: To find the average product sales from a specific region, category, and month.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Category | Month | Formula | Result |
| 2 | Product A | 100 | Region 1 | Cat 1 | Jan | ||
| 3 | Product B | 200 | Region 2 | Cat 2 | Feb | ||
| 4 | Product C | 300 | Region 1 | Cat 1 | Jan | ||
| 5 | Average | =AVERAGEIFS(B2:B4, C2:C4, "Region 1", D2:D4, "Cat 1", E2:E4, "Jan") | 200 |
Explanation: This formula calculates the average sales of products from Region 1 and Category 1 sold in January. Products A and C meet the criteria in this case, so the average sales is 200.
Example 6: AVERAGEIFS with IF
Purpose: To find the average product sales from a specific region, and if the average is more significant than 200, return “High”. Otherwise, return “Low”.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Formula | Result |
| 2 | Product A | 100 | Region 1 | ||
| 3 | Product B | 200 | Region 2 | ||
| 4 | Product C | 300 | Region 1 | ||
| 5 | Average | =IF(AVERAGEIFS(B2:B4, C2:C4, "Region 1")>200, "High", "Low") | High |
Explanation: This formula first calculates the average sales of products from Region 1 using the AVERAGEIFS function. Then, the IF function checks if this average is more significant than 200. If it is, the formula returns “High”. If not, it replaces “Low”. In this case, the average sales in Region 1 are 200, resulting in “High”.
Example 7: AVERAGEIFS with SUM
Purpose: To find the total average sales of products from two different regions.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Formula | Result |
| 2 | Product A | 100 | Region 1 | ||
| 3 | Product B | 200 | Region 2 | ||
| 4 | Product C | 300 | Region 1 | ||
| 5 | Average | =SUM(AVERAGEIFS(B2:B4, C2:C4, "Region 1"), AVERAGEIFS(B2:B4, C2:C4, "Region 2")) | 250 |
Explanation: This formula calculates the average sales of products from Region 1 and Region 2 separately using the AVERAGEIFS function. Then, the SUM function adds these two averages together. In this case, the average sales in Region 1 is 200, and in Region 2 is 200, so the total average is 250.
Example 8: AVERAGEIFS with VLOOKUP
Purpose: To find the average product sales from a specific region and then look up the commission rate for that average from another table.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Formula | Result |
| 2 | Product A | 100 | Region 1 | ||
| 3 | Product B | 200 | Region 2 | ||
| 4 | Product C | 300 | Region 1 | ||
| 5 | Average | =VLOOKUP(AVERAGEIFS(B2:B4, C2:C4, "Region 1"), G2:H4, 2, FALSE) | 5% |
Commission Table
| G | H | |
|---|---|---|
| 1 | Sales | Rate |
| 2 | 100 | 3% |
| 3 | 200 | 5% |
| 4 | 300 | 7% |
Explanation: This formula first calculates the average sales of products from Region 1 using the AVERAGEIFS function. Then, the VLOOKUP function looks up this average in the commission table and returns the corresponding commission rate. In this case, the average sales in Region 1 is 200, so the commission rate is 5%.
Example 9: AVERAGEIFS with COUNTIF
Purpose: To find the average product sales from a specific region and then count how many products have sales above this average.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Formula | Result |
| 2 | Product A | 100 | Region 1 | ||
| 3 | Product B | 200 | Region 2 | ||
| 4 | Product C | 300 | Region 1 | ||
| 5 | Average | =COUNTIF(B2:B4, ">"&AVERAGEIFS(B2:B4, C2:C4, "Region 1")) | 1 |
Explanation: This formula first calculates the average sales of products from Region 1 using the AVERAGEIFS function. Then, the COUNTIF function counts how many products have sales above this average. In this case, the average sales in Region 1 is 200, and only Product C has sales above this average, so the result is 1.
Example 10: AVERAGEIFS with MAX
Purpose: To find the highest average sales among two different regions.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Formula | Result |
| 2 | Product A | 100 | Region 1 | ||
| 3 | Product B | 200 | Region 2 | ||
| 4 | Product C | 300 | Region 1 | ||
| 5 | Average | =MAX(AVERAGEIFS(B2:B4, C2:C4, "Region 1"), AVERAGEIFS(B2:B4, C2:C4, "Region 2")) | 200 |
Explanation: This formula calculates the average sales of products from Region 1 and Region 2 separately using the AVERAGEIFS function. Then, the MAX function returns the highest of these two averages. In this case, the average sales in Region 1 is 200 and in Region 2 is 200, so the highest average is 200.
Example 11: AVERAGEIFS with MIN
Purpose: To find the lowest average sales among two different regions.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Formula | Result |
| 2 | Product A | 100 | Region 1 | ||
| 3 | Product B | 200 | Region 2 | ||
| 4 | Product C | 300 | Region 1 | ||
| 5 | Average | =MIN(AVERAGEIFS(B2:B4, C2:C4, "Region 1"), AVERAGEIFS(B2:B4, C2:C4, "Region 2")) | 200 |
Explanation: This formula calculates the average sales of products from Region 1 and Region 2 separately using the AVERAGEIFS function. Then, the MIN function returns the lowest of these two averages. In this case, the average sales in Region 1 is 200 and in Region 2 is 200, so the lowest average is 200.
Example 12: AVERAGEIFS with ROUND
Purpose: To find the average product sales from a specific region and then round this average to the nearest whole number.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Formula | Result |
| 2 | Product A | 100 | Region 1 | ||
| 3 | Product B | 210 | Region 2 | ||
| 4 | Product C | 300 | Region 1 | ||
| 5 | Average | =ROUND(AVERAGEIFS(B2:B4, C2:C4, "Region 1"), 0) | 200 |
Explanation: This formula first calculates the average sales of products from Region 1 using the AVERAGEIFS function. Then, the ROUND function rounds this average to the nearest whole number. In this case, the average sales in Region 1 is 200, so the rounded average is 200.
Example 13: AVERAGEIFS with SUMPRODUCT
Purpose: To find the average product sales from a specific region and then multiply this by the total number of products.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | Region | Formula | Result |
| 2 | Product A | 100 | Region 1 | ||
| 3 | Product B | 200 | Region 2 | ||
| 4 | Product C | 300 | Region 1 | ||
| 5 | Average | =SUMPRODUCT(AVERAGEIFS(B2:B4, C2:C4, "Region 1"), COUNTA(A2:A4)) | 600 |
Explanation: This formula first calculates the average sales of products from Region 1 using the AVERAGEIFS function. Then, the SUMPRODUCT function multiplies this average by the total number of products (counted using the COUNTA function). In this case, the average sales in Region 1 is 200, and there are 3 products, so the result is 600.
Part 3: Tips and Tricks
- Use the wildcard characters, question mark (?), and asterisk (*) in the criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
- Be careful with text and blank values in your average range, as AVERAGEIFS will return the #DIV0! Error value.
- Make sure each criteria_range is the same size and shape as average_range. If not, you’ll get an error.
- If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
- Remember that cells in a range containing TRUE are evaluated as 1; cells in a field with FALSE are considered 0.