How to use the AVERAGEIF function

What is the AVERAGEIF function?

The AVERAGEIF function returns the average of cell values that are valid for a given condition. The AVERAGEIF function is available for Excel 2010 users and later versions.

1. AVERAGEIF Function Syntax

AVERAGEIF(range, criteria, [average_range])

Back to top

2. AVERAGEIF Function Arguments

range	Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
criteria	Required. A cell reference, expression or text that determines which values to be evaluated.
average_range	Optional. Cells to average. If not entered, range is used.

Back to top

3. Which values are excluded in the AVERAGEIF function?

1. The AVERAGIF function excludes Boolean values TRUE or FALSE in the calculation, see the example in cell C5 in the image above.
   Cell C5 contains the boolean value TRUE, the corresponding value in cell B5 matches the condition specified in cell E3. The boolean value TRUE is ignored nonetheless.
2. Empty blank cells in argument average_range is excluded from the calculation, see cell C3 in the picture above, despite the matching corresponding value in cell B3.

Back to top

4. AVERAGEIF Function not working

• The AVERAGEIF function returns #DIV0! if no values match the criteria.
• A #NAME error translates to a misspelled function in your formula.
• The AVERAGEIF function ignores text and boolean values, however, not error values.
• Trying to use an asterisk or question mark as a condition? It won't work as they are wildcard characters, however, there is a workaround. Use the ~ (tilde) character to escape wildcard characters.

Back to top

5. How to use wildcard characters in the AVERAGEIF function

It is possible to use wildcard characters like (?) question mark or (*) asterisk.

(?) question mark matches any single character.

(*) asterisk matches any sequence of characters even zero charcaters.

Use a tilde (~) before to escape the wildcard characters meaning finding the actual question mark or asterisk.

The following examples demonstrate how to use wildcard characters in the AVERAGEIF function.

6 AVERAGEIF function - begins with

The image above demonstrates how to average corresponding numbers if a value on the same row begins with a specific condition.

Cell range B3:C7 contains the data table, the column header names are "Value" and "Numbers". The condition is specified in cell E3.

Formula in cell F3:

The formula above appends a asterisk character after the condition, this makes the formula match values that begins with the condition. Cells B3 and B6 both begin with the condition specified in cell E3. The corresponding numbers in column C are 8 and 3.

We can now calculate the average. 8+3 = 11. 11 / 2 = 5.5 This number is the same as the number in cell F3. The formula is working as intended.

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - E3&"*"
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, E3&"*", C3:C7)

becomes

AVERAGEIF({"A-101"; "B-104"; "B-103"; "A-105"; "B-102"}, "A*", {8; 3; 5; 3; 2})

and returns 5.5

8 + 3 = 11

11/2 equals 5.5

Back to top

7 AVERAGEIF function - ends with

The image above shows a formula in cell F3 that calculates an average if the values in cell range B3:B7 ends with a given condition specified in cell E3.

The corresponding number on the same row in cell range C3:C7 is included in the calculation if the value meets the condition.

Formula in cell F3:

Cells B5 and B7 meet the condition, corresponding cells are C5 and C7. They contain 5 and 2. 5 + 2 equals 7. 7 / 2 equals 3.5

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - "*"&E3
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, "*"&E3, C3:C7)

becomes

AVERAGEIF({"A-101"; "B-104"; "B-102"; "A-105"; "B-102"}, "*02", {8; 3; 5; 3; 2})

and returns 3.5

5 + 2 = 7

7/2 equals 3.5

Back to top

8 AVERAGEIF function - contains

This example demonstrates a fomrula that calculates an average if the corresponding values on the same rows contain a specific condition. In other words, its a partial match meaning the conditions must be found somewhere in the cell value.

The image above shows a data table in cell B3:C7 and the table header names are "Value and Numbers. The condition is specified in cell E3.

Formula in cell F3:

The formula in cell F3 appends asterisks to the beginning and the end of the condition. The ampersand concatenates value to value, the double quotes are used to specify text values in a formula.

Cells B3, and B7 contain the specified condition in cell E3, the numbers on the same rows in column C are 8 and 2. We can now calculate the average: 8 + 2 = 10. 10 / 2 equals 5 which is the same value as in cell F3. The formula in cell F3 works!

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - "*"&E3&"*"
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, "*"&E3&"*", C3:C7)

becomes

AVERAGEIF({"A-101"; "B-204"; "B-203"; "A-205"; "B-102"}, "*1*", {8; 3; 5; 3; 2})

and returns 5.

8 + 2 = 10

10/2 equals 5

Back to top

9 AVERAGEIF function - match a specific number of characters

The question mark character ? lets you match any single character in the condition string. The above image demonstrates a formula that uses the following criteria "A-???". In other words, it matches A- and then 3 single characters only.

It matches values in cells B3 and B6. Cell B4 contains a value that almost match, however, it is longer than the requirements. Remember that the question mark only matches a single character.

Formula in cell F3:

The corresponding numbers on the same rows in column C are 8 and 3. 8+3 = 11. 11 / 2 = 5.5 The same value is displayed in cell F3.

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - E3
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, E3, C3:C7)

becomes

AVERAGEIF({"A-101"; "A-1342"; "AA-802"; "A-205"; "B-102"}, "A-???", {8; 3; 5; 3; 2})

and returns 5.5

8 + 3 = 11

11/2 equals 5.5

Back to top

10. Is the criteria argument in the AVERAGEIF function case sensitive?

Does the AVERAGEIF function differentiate between upper and lower letters?
No, the image above shows that the AVERAGEIF function does not consider upper and lower case letters. In other words, the AVERAGEIF function is not case sensitive. The image above shows a data table in cell range B3:C7, it has the following table header names: "Value" and "Numbers".

The condition in cell E3 is uppercase letter "A", however it matches both the lower case letter in cell B6 and the rightly the upper case letter in cell B7. 3+2 = 5. 5/2 = 2.5. This is not what we want. However, the image below describes a workaround. Here is how to:

The image above shows a formula in cell F3 that considers upper and lower cases. In other words it is case sensitive. The data table in cell range B3:C7 has these table header names: Value and Numbers.

The condition in cell E3 is the upper case letter "A". It matches cells B3 and B7 but not cell B6 which contains lower case letter "a".

Array formula in cell F3:

The corresponding numbers in column c are 8 and 2. The average is calculated like this: 8+2 = 10. 10/2 = 5. This value matches the value in cell F3, the formula works!

How to enter an array formula

1. Doublepress with left mouse button on cell F3, the prompt appears.
2. Type or paste the array formula.
3. Press and hold CTRL + SHIFT keys simultaneously.
4. Press Enter once.
5. Release all keys.

The formula is now entered as an array formula, the formula begins with and ends with a curly bracket, see the image above.

Don't enter these characters yourself, they appear automatically.

Explaining formula

Step 1 - Case sensitive comparison

The EXACT function returns TRUE if two values match also considering upper and lower letters.

EXACT(value1, value2)

EXACT(E3, B3:B7)

becomes

EXACT("A",{"A";"B";"B";"a";"A"})

and returns

{TRUE; FALSE; FALSE; FALSE; TRUE}.

Step 2 - Replace TRUE with corresponding value

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(EXACT(E3, B3:B7), C3:C7, "")

becomes

IF({TRUE; FALSE; FALSE; FALSE; TRUE}, C3:C7, "")

becomes

IF({TRUE; FALSE; FALSE; FALSE; TRUE}, {8; 3; 5; 3; 2}, "")

and returns

{8; ""; ""; ""; 2}.

Step 3 - Calculate average

The AVERAGE function calculates the average of numbers in a cell range.

AVERAGE(number1, [number2], ...)

AVERAGE(IF(EXACT(E3,B3:B7),C3:C7,""))

becomes

AVERAGE({8; ""; ""; ""; 2})

and returns 5.

8 + 2 = 10

10/2 = 5

Back to top

11. AVERAGEIF function - comparison operators

You are allowed to use the following comparison operators:

• < less than character
• > larger than character
• <> not equal to
• <= less than or equal to
• >= larger than or equal to

The equal sign is never used alone with the value in the criteria argument, the image above shows how to use a condition specified in cell E3 in the AVERAGEIF function.

Back to top

12. AVERAGEIF function - larger than

This example shows the AVERAGEIF function filtering values larger than a given threshold and calculating the average of the corresponding values on the same row.

The image above shows a data table in cell range B2:C7, it has table header names: "Value" and "Numbers. The condition is specified in cell E3 and is >100.

Value	Numbers
100	5
105	3
94	6
97	3
102	2

The formula filters values from column C if the corresponding values in column B are above 100. The greater than sign is a comparison operator that you can use to apply more complicated calculations and filtering.

Formula in cell F3:

Cells B4 and B7 meet the condition, both contain numbers larger than 100, the corresponding numbers in column C on the same row are: 3 and 2. We can now calculate the average by adding the numbers and the divide by the total number of observations.

3 + 2 = 5. 5/2 = 2.5 which is the same as in cell F3. The formula works fine.

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - E3
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, E3, C3:C7)

becomes

AVERAGEIF({100;105;94;97;102},">100",{5;3;6;3;2})

and returns 2.5

3 + 2 = 5

5/2 equals 2.5

Back to top

13. AVERAGEIF function - smaller than

This example shows the AVERAGEIF function filtering values smaller than a given threshold and then calculating the average of the corresponding values on the same row.

The image above shows a data table in cell range B2:C7, it has table header names: "Value" and "Numbers. The condition is specified in cell E3 and is <100.

Formula in cell F3:

Cells B5 and B6 meet the condition, both contain numbers smaller than 100, the corresponding numbers in column C on the same row are: 6 and 3. We can now calculate the average by adding the numbers and the divide by the total number of observations.

6 + 3 = 9. 9/2 = 4.5 which is the same as in cell F3. The formula works as intended.

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - E3
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, E3, C3:C7)

becomes

AVERAGEIF({100;105;94;97;102},"<100",{5;3;6;3;2})

and returns 2.5

6 + 3 = 9

9/2 equals 4.5

Back to top

14. AVERAGEIF function - not equal to

This example shows the AVERAGEIF function filtering values not equal to a given condition and then calculating the average of the corresponding values on the same row.

The image above shows a data table in cell range B2:C7, it has table header names: "Value" and "Numbers. The condition is specified in cell E3 and is <100.

Formula in cell F3:

Cells B4, B5, B6, and B7 meet the condition, all contain numbers not equal to 100, the corresponding numbers in column C on the same row are: 3, 6, 3, and 2. We can now calculate the average by adding the numbers and the divide by the total number of observations.

3+6+3+2 = 14. 14/4 = 3.5 which is the same as the calculated value in cell F3.

Explaining formula

Step 1 - Populate arguments

AVERAGEIF(range, criteria, [average_range])

range - B3:B7
criteria - E3
[average_range] - C3:C7

Step 2 - Evaluate AVERAGEIF function

AVERAGEIF(B3:B7, E3, C3:C7)

becomes

AVERAGEIF({100;105;94;97;102},"<>100",{5;3;6;3;2})

and returns 2.5

3 + 6 + 3 + 2 = 14

14/4 equals 3.5

Back to top

'AVERAGEIF' function examples

Functions in 'Statistical' category

The AVERAGEIF function function is one of 73 functions in the 'Statistical' category.

Excel function categories

Excel categories

Leave a Comment