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

### Table of Contents

- AVERAGEIF Function Syntax
- AVERAGEIF Function Arguments
- Which values are excluded in the calculation?
- AVERAGEIF function not working
- How to use wildcard characters in the AVERAGEIF function
- AVERAGEIF function - begins with
- AVERAGEIF function - ends with
- AVERAGEIF function - contains
- AVERAGEIF function - match a specific number of characters
- Is the criteria argument in the AVERAGEIF function case sensitive?
- AVERAGEIF function - comparison operators
- AVERAGEIF function - larger than
- AVERAGEIF function - smaller than
- AVERAGEIF function - not equal to

## 1. AVERAGEIF Function Syntax

AVERAGEIF(*range*, *criteria*, [*average_range*])

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

## 3. Which values are excluded in the AVERAGEIF function?

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

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

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

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

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

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

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

- Doublepress with left mouse button on cell F3, the prompt appears.
- Type or paste the array formula.
- Press and hold CTRL + SHIFT keys simultaneously.
- Press Enter once.
- 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

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

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

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

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

### 'AVERAGEIF' function examples

Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]

The AVERAGE function ignores empty cells, text values, and boolean values automatically, however, it doesn't handle error values. The AVERAGE […]

### Functions in 'Statistical' category

The AVERAGEIF 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 signsUse 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 OscarYou can contact me through this contact form