How to use the AVERAGEIFS function
What is the AVERAGEIFS function?
The AVERAGEIFS function returns the average of cell values that evaluates to TRUE for multiple criteria.
Table of Contents
1. Introduction
What is an average?
An average is a statistical measure of the central or typical value in a data set that provides an estimation of expected results by calculating the sum of values divided by total occurrences. Common averages include the arithmetic mean, median, and mode, each of which summarize data in different ways.
The AVERAGEIFS function calculates an arithmetic mean. For example, here is a group containing 4 numbers: 4, 2, 5, 3 The average is calculated like this: 4+2+5+3 = 14
14/4 = 3.5
3.5 is the arithmetic mean of 4, 2, 5, 3
What is IFS in the AVERAGEIFS function?
This means that the function allows you to specify a condition or critera in order to filter the values you want to calculate an average of.
2. AVERAGEIFS Function Syntax
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2] , [criteria2], ...)
The AVERAGEIFS function allows you to have up to 127 criteria and criteria_range arguments.
3. AVERAGEIFS Function Arguments
| average_range | Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers. |
| criteria_range1 | Required. A cell range in which to evaluate the corresponding condition |
| criteria1 | Required. A condition in the form of a cell reference, expression or text that determines which values to be averaged. |
| [criteria_range2] | Optional. You are allowed to have multiple criteria ranges. |
| [criteria2] | Optional. You are allowed to have multiple criteria. |
4. What values are excluded in the AVERAGEIFS Function?
Boolean values TRUE or FALSE are excluded in the calculation. All empty blank cell in argument average_range are also excluded.
5. AVERAGEIFS function not working
Here are some recommendations you may want to check if your AVERAGEIFS function is not working.
- A blank or text value in the argument range is not valid, the function returns #DIV0!.
- A value is used in the calculation only if all of the corresponding criteria specified are true for that cell.
- If a condition in argument average_range is empty is the same as 0 (zero).
- The AVERAGEIFS function returns #DIV0! if no values match the criteria.
6. AVERAGEIFS Function Example
This example demonstrates how to use the AVERAGEIFS function with three conditions. The image above shows a data table in cell range B3:E8, it has 4 columns with these column header names: Item, Level, Color, and Price.
The conditions are specified below the data table in cell range B11:D11. The formula in cell E11 uses comparison operators to create advanced conditions. They allow you to specify conditions that are smaller, larger or not equal to the conditions specified in cell B11:D11.
Formula in cell E11:
The formula in cell E11 in the picture above calculates the average for values in cell range E3:E8 that match all the conditions on the same row. It checks i cells in B3:B8 match the value in B11 meaning the entire cell value must be equal to the entire cell value in cell B11. You don't need to specify the equal sign to compare values, the formula compares values in its default state.
The next condition checks if the numbers in cell range C3:C8 are smaller than the value in C11. The ampersand character lets you append a comparison character to the cell reference. <"&C11 You can also enter the comparison operators in the cells B11:D11 if you want and skip the ampersand all together.
The third and last condition is specified in cell D11. This condition uses the less than and greater than signs combined meaning "not equal to". This means that all values that doesn't match the value in cell D11 is a match. Confusing?
When we apply all the criteria to the data table we get the following values:
- 3 from cell E4. B4 matches B11, C4 is smaller than C11, D4 is not equal to D11.
- 2 from cell E7. B7 matches B11, C7 is smaller than C11, D7 is not equal to D11.
We can now calulate the average: 3+2 equals 5. 5 / 2 equals 2.5 which is the same value in cell E11.
Explaining formula
The AVERAGEIFS function lets you use comparison operators, they are:
- < less than character
- > larger than character
- <= less than or equal to
- >= larger than or equal to
- <> not equal to
Step 1 - AVERAGEIFS function
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2] , [criteria2], ...)
Step 2 - Populate arguments
average_range - E3:E8
criteria_range1 - B3:B8
criteria1 - B11
criteria_range2 - C3:C8
criteria2 - "<"&C11
criteria_range3 - D3:D8
criteria3 - "<>"&D11
Step 3 - Evaluate function
AVERAGEIFS(D3:D8, B3:B8, F3, C3:C8, G3)
becomes
AVERAGEIFS({5; 3; 6; 3; 2; 4}, {"A"; "B"; "A"; "B"; "B"; "A"}, "B", {1; 2; 3; 1; 2; 3}, "<"&3, {"Black"; "White"; "Black"; "Black"; "White"; "White"}, "<>"&"Black")
and returns 2.5
First condition matches cells B4,B6, and B7.
The second condition matches C3, C4, C6, and C7.
The third condition matches D4, D7, and D8.
Rows that match all conditions are 4 and 7, cells E4 and E7 contain 3 and 2. 3 + 2 equals 5. 5/2 is 2.5
7. AVERAGEIFS Function - wildcard characters
The comparison operators described above are not the only characters you can use. It is also possible to use wildcard characters like (?) question mark or (*) asterisk.
- (?) question mark matches any single character.
- (*) asterisk matches any sequence of characters.
- Use a tilde (~) before to escape the wildcard characters meaning finding the actual question mark or asterisk.
The following examples below demonstrate how to use these characters.
7.1 AVERAGEIFS Function - begins with
The image above demonstrates a formula in cell C11 that average numbers if values on the same row begin with a specific condition.
The data table is in cell B3:C7 and has the following header names: Value and Numbers. The condition is specified in cell B11 and the formula below uses a asterisk character to match any character.
This means that the formula matches values in B3:B7 that begins with the string specified in cell B11. Note that this operation is not case- sensitive meaning it matches the string without considering upper and lower characters.
Formula in cell F3:
The formula in cell C11 matches the following cell values:
- B3 - "LV-104". This value begins with the same string specified in cell B11. The corresponding value in column C is 8.
- B11 - "LV-305". This value begins with the same string specified in cell B11. The corresponding value in column C is 3.
We can now calculate the average based of these two numbers. 8 + 3 = 11. 11 / 2 equals 5.5 which is the value in cell C11.
Explaining formula
Step 1 - Populate arguments
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2] , [criteria2], ...)
average_range - B3:B7
criteria - E3&"*"
criteria_range - C3:C7
Step 2 - Evaluate AVERAGEIF function
AVERAGEIFS(C3:C7, B3:B7, B11&"*")
becomes
AVERAGEIFS({8; 3; 5; 3; 2}, {"LV-104"; "BV-10"; "BC-201"; "LV-305"; "BV-209"}, "LV*")
and returns 5.5
Cells B3 and B6 begin with the given value in cell B11, the corresponding values are in C3 and C6. They contain 8 and 3, 8 + 3 equals 11. 11/2 equals 5.5
7.2 AVERAGEIFS Function - ends with
The image above shows a data table in cell B3:C7 with the following column names: Value and Numbers. The condition is specified in cell B11.
The formula in cell C11 calculates an average based on numbers in C3:C7 if the corresponding value in cell range B3:B7 does NOT end with the value specified in cell B, in this example, 1.
Formula in cell C11:
In other words, the formula matches values in cell range B3:B7 that don't end with number 1. "<>*"&B11 contains less than and greater than signs combined meaning not equal to and the asterisk. The asterisk match any character and any number of characters.
Cells B3, B4, B6, and B7 contain values that don't end with 1, the corresponding numbers on the same rows are 8, 3, 3, and 2. 8 + 3 + 3 + 2 equals 16. 16/4 equals 4
Explaining formula
Tip! You can use the "Evaluate" tool found on tab "Formulas" on the ribbon to examine the formula step by step. This makes it easier to troubleshoot and understand more complicated formulas.
Step 1 - Populate arguments
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2] , [criteria2], ...)
average_range - B3:B7
criteria - "<>*"&B11
criteria_range - C3:C7
Step 2 - Evaluate AVERAGEIF function
AVERAGEIFS(C3:C7, B3:B7, "<>*"&B11)
becomes
AVERAGEIFS({8; 3; 5; 3; 2}, {"LV-104"; "BV-10"; "BC-201"; "LV-305"; "BV-209"}, "<>*1")
and returns 4.
7.3 AVERAGEIFS Function - contains a string
This example shows data table in cell B3:C7, the column header names are: Value and Number.
The AVERAGIFS function in cell C11 calculates an average based on numbers in cell range C3:C7, if the corresponding value on the same row in cell range B3:B7 contains the condition specified in cell B11.
In other words, this is different than the other examples above meaning a match is found if a substring specified in cell B11 is found in a cell value in cell range B3:B7. This is not a case sensitive search meaning upper and lower characters are not considered different.
Formula in cell C11:
The formula above appends asterisks to both the beginning and the end of the condition. The condition in cell B11 matches cells B3, B4, B6, and B7. The corresponding values in column C are: 8, 3, 3, and 2.
The average is calculated like this: 8+3+3+2 equals 16. 16 / 4 equals 4. The average is 4 and cell C11 shows the same value.
Explaining formula
Step 1 - Populate arguments
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2] , [criteria2], ...)
average_range - C3:C7
criteria_range1 - B3:B7
criteria - "*"&B11&"*"
Step 2 - Evaluate AVERAGEIF function
AVERAGEIFS(C3:C7, B3:B7, "<>*"&B11)
becomes
AVERAGEIFS({8; 3; 5; 3; 2}, {"LV-104"; "BV-10"; "BC-201"; "LV-305"; "BV-209"}, "*V*")
and returns 4.
8. AVERAGEIFS Function - caveats
The AVERAGEIFS function will not accept arrays or nested functions in any of the arguments which makes the AVERAGEIFS function not that useful for more advanced calculations in my opinion.
The image above demonstrates the AVERAGEIFS function nested with the TEXTSPLIT and INDEX function, unfortunately, it doesn't work.
What if we use "helper"-cells to perform intermediate calculations? Cell B5 contains: TEXTSPLIT(B3, ",", ";"). It is a dynamic array formula that spills values to cells below and to the right based on delimiting characters.
No, the AVERAGEIFS function returns a #DIV/0! error. The AVERAGEIFS function seems to be incompatible with new dynamic array functions in Excel 365.
Referencing dynamic arrays won't work either:
This returns a #DIV/0! error as well.
Functions in 'Statistical' category
The AVERAGEIFS function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form