Author: Oscar Cronquist Article last updated on May 05, 2022

The AVERAGEIF function returns the average of cell values that are valid for a given condition.

Formula in cell D3:

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

The AVERAGEIF function is available for Excel 2010 users and later versions.

1. Excel Function Syntax

AVERAGEIF(range, criteria, [average_range])

Back to top

2. 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?

AVERAGEIF Function excluded values

Boolean values TRUE or FALSE are excluded in the calculation, see cell C5 in the image above.

An empty cell in argument average_range is excluded, see cell C3 in the picture above.

If a condition in argument average_range is empty is the same as 0 (zero).

Back to top

4. AVERAGEIF Function not working

AVERAGEIF Function name error

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.

5.1 AVERAGEIF function - begins with

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.

Formula in cell F3:

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

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

5.2 AVERAGEIF function - ends with

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.

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

Formula in cell F3:

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

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

5.3 AVERAGEIF function - contains

AVERAGEIF Function contains

Formula in cell F3:

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

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

5.4 AVERAGEIF function - match a specific number of characters

AVERAGEIF Function 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-???"

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:

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

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

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

AVERAGEIF Function case sensitive

Does the AVERAGEIF function differentiate between upper and lower letters?
No, the image above shows that it's not. Here is how to:

AVERAGEIF Function case sensitive1

Array formula in cell F3:

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

How to enter an array formula

AVERAGEIF Function 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

7. AVERAGEIF function - comparison operators

AVERAGEIF Function equal to

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

7.1 AVERAGEIF function - larger than

AVERAGEIF Function larger than

Formula in cell F3:

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

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

7.2 AVERAGEIF function - smaller than

AVERAGEIF Function smaller than

Formula in cell F3:

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

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

7.3 AVERAGEIF function - not equal to

AVERAGEIF Function not equal to

Formula in cell F3:

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

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