# 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:

syntax
`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.

ABCD
1ProductSalesFormulaResult
2Product A100
3Product B200
4Product C300
5Average`=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.

ABCDE
1ProductSalesRegionFormulaResult
2Product A100Region 1
3Product B200Region 2
4Product C300Region 1
5Average`=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.

ABCDEF
1ProductSalesRegionCategoryFormulaResult
2Product A100Region 1Cat 1
3Product B200Region 2Cat 2
4Product C300Region 1Cat 1
5Average`=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.

ABCDEF
1ProductSalesRegionCategoryFormulaResult
2Product A100Region 1Cat 1
3Product B200Region 2Cat 2
4Product C300Region 1Cat 1
5Average`=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.

ABCDEFG
1ProductSalesRegionCategoryMonthFormulaResult
2Product A100Region 1Cat 1Jan
3Product B200Region 2Cat 2Feb
4Product C300Region 1Cat 1Jan
5Average`=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”.

ABCDE
1ProductSalesRegionFormulaResult
2Product A100Region 1
3Product B200Region 2
4Product C300Region 1
5Average`=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.

ABCDE
1ProductSalesRegionFormulaResult
2Product A100Region 1
3Product B200Region 2
4Product C300Region 1
5Average`=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.

ABCDE
1ProductSalesRegionFormulaResult
2Product A100Region 1
3Product B200Region 2
4Product C300Region 1
5Average`=VLOOKUP(AVERAGEIFS(B2:B4, C2:C4, "Region 1"), G2:H4, 2, FALSE)`5%

Commission Table

GH
1SalesRate
21003%
32005%
43007%

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.

ABCDE
1ProductSalesRegionFormulaResult
2Product A100Region 1
3Product B200Region 2
4Product C300Region 1
5Average`=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.

ABCDE
1ProductSalesRegionFormulaResult
2Product A100Region 1
3Product B200Region 2
4Product C300Region 1
5Average`=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.

ABCDE
1ProductSalesRegionFormulaResult
2Product A100Region 1
3Product B200Region 2
4Product C300Region 1
5Average`=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.

ABCDE
1ProductSalesRegionFormulaResult
2Product A100Region 1
3Product B210Region 2
4Product C300Region 1
5Average`=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.

ABCDE
1ProductSalesRegionFormulaResult
2Product A100Region 1
3Product B200Region 2
4Product C300Region 1
5Average`=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

1. 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.
2. Be careful with text and blank values in your average range, as AVERAGEIFS will return the #DIV0! Error value.
3. Make sure each criteria_range is the same size and shape as average_range. If not, you’ll get an error.
4. If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
5. Remember that cells in a range containing TRUE are evaluated as 1; cells in a field with FALSE are considered 0.