Using the Excel AVERAGEIF function

The AVERAGEIF function finds all of the values in a range that meet the criteria you specify and either averages them or averages the values in a corresponding range.

Syntax:

AVERAGEIF( range, criteria, [range to average] )

Range - The range of values to test the criteria against. Also, the range of values to average if no "range to average" is specified.

Criteria - The condition that must be met for a value to be included in the average.

Range to average - The range of values to be averaged (optional ).

The criteria can be a date, a time, an integer, a decimal, a logical (TRUE or FALSE), text, or a logical expression. A logical expression is an expression that includes a comparison operator, for example ">10".  Text and logical expressions must be enclosed in quotes.  Text criteria is not case sensitive, so "DOG" and "dog" are equivalent.

You can use wildcards with text criteria. The asterisk  (*) matches any sequence of characters. For example, ABC*123 matches, ABC---123, ABC-/@4-123 and ABC+123. The question mark (?) matches any single character. For example, ABC?123 matches, ABC-123, ABC/123 and ABCD123. It will not match ABC---123 or ABC-/@4-123. If you want to match the asterisk (*) or the question mark, place a tilde (~) before the character.

Example 1: You have a list of incomes in cells B2 through B11. You want to find the average of all incomes that are less than 100,000 dollars.

=AVERAGEIF(B2:B11,"<100000")

Example 2: You have two teams: Team One and Team Two. You want to find the average score for Team One. The team names are in cells B2 through B11. The scores are in cells C2 through C11.

=AVERAGEIF(B2:B11,"Team One",C2:C11)

Example 3:

You have two sales regions: Region One and Region Two. They report sales monthly. The fields are labeled "January - Region One," February - Region One," and so on. You want to find the average sales for Region One without regard to month. The region names are in cells A2 through A13. The sales are in cells B2 through B13.

=AVERAGEIF(A2:A13,"*Region One",B2:B13)

In the following worksheet, all of the cells with orange text contain a calculation. Double click in the field to display the formula. Feel free to use the worksheet to experiment in any way you like.