Excel AVERAGIFS Function
The Excel AVERAGEIFS function returns the average of the numbers in a specified range based on multiple criteria
Example: Excel AVERAGIFS Function
|
=AVERAGEIFS(E9:E14,B9:B14,2017,D9:D14,"Bread")
|
Result in cell G9 (5,485) - returns the averages of all numbers in range (E9:E14) that are associated with 2017 and Bread from ranges (B9:B14) and (D9:D14), respectively.
|
|
=AVERAGEIFS(E9:E14,B9:B14,C5,D9:D14,C6)
|
Result in cell G9 (5,485) - returns the average of all numbers in range (E9:E14) that are associated with the value in cells (C5) and (C6) from ranges (B9:B14) and (D9:D14), respectively.
|
METHOD 3. Excel AVERAGEIFS function using the Excel built-in function library with hardcoded values
EXCEL
| =AVERAGEIFS(E9:E14,B9:B14,2017,D9:D14,"Bread") Note: in this example we are averaging the numbers in range (E9:E14) that are associated with 2017 and Bread from ranges (B9:B14) and (D9:D14), respectively. |
 |
METHOD 4. Excel AVERAGEIFS function using the Excel built-in function library with links
EXCEL
| =AVERAGEIFS(E9:E14,B9:B14,C5,D9:D14,C6) Note: in this example we are averaging the numbers in range (E9:E14) that are associated with the value in cells (C5) and (C6) from ranges (B9:B14) and (D9:D14), respectively. |
 |
Dim ws As Worksheet
ws.Range("G9") = Application.WorksheetFunction.AverageIfs(ws.Range("E9:E14"), ws.Range("B9:B14"), 2017, ws.Range("D9:D14"), "Bread")
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named AVERAGEIFS.
Average Range: Ensure that the data you want average is captured in range ("E9:E14").
Range: Ensure that the corresponding ranges (range1 and range2) to the average range are captured in ranges ("B9:B14") and ("D9:D14"), respectively.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the range references ("G9") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Average Range: Select the range that you want to average by changing the range ("E9:E14") to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the corresponding ranges (range1 and range2) to the average range by changing the ranges ("B9:B14") and ("D9:D14"), respectively, to any range in the worksheet, that doesn't conflict with the formula.
Dim ws As Worksheet
ws.Range("G9") = Application.WorksheetFunction.AverageIfs(ws.Range("E9:E14"), ws.Range("B9:B14"), ws.Range("C5"), ws.Range("D9:D14"), ws.Range("C6"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named AVERAGEIFS.
Average Range: Ensure that the data you want average is captured in range ("E9:E14").
Range: Ensure that the corresponding ranges (range1 and range2) to the average range are captured in ranges ("B9:B14") and ("D9:D14"), respectively.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the range references ("G9") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Average Range: Select the range that you want to average by changing the range ("E9:E14") to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the corresponding ranges (range1 and range2) to the average range by changing the ranges ("B9:B14") and ("D9:D14"), respectively, to any range in the worksheet, that doesn't conflict with the formula.
The Excel AVERAGEIFS function returns the average of all numbers in a specified range based on multiple criteria.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)
average_range: (Required) The range of cells you want to average from.
criteria_range1: (Required) The range of cells that you want to test criteria1 against.
criteria1: (Required) The criteria that is used to determine which of the cells, from range1, should be averaged.
criteria_range2: (Optional) The range of cells that you want to test criteria2 against.
criteria2: (Optional) The criteria that is used to determine which of the cells, from range2, should be averaged.
ADDITIONAL NOTES
Note 1: The AVERAGEIFS function allows the use of logical elements (>,<,<>,=).
Note 2: The AVERAGEIFS function allows the use of wildcards:
* searches to find a match for the sequence of characters.
? searches to find a match to any single character.