What is the Excel AVERAGEIF Function?
The Excel AVERAGEIF function returns the average (arithmetic mean) of values supplied that meet criteria.
AVERAGEIF Syntax
AVERAGEIF(range, criteria, [average_range])
range, required, cell, range or reference containing numbers to average
criteria, required, criteria in the form of text, cell reference, expression or a number.
average_range, optional, The actual range to average. When omitted, the range argument is used.
Usage Notes:
- AVERAGEIF function ignores logical values (TRUE/FALSE).
- AVERAGEIF function ignores empty cells in the average_range
- AVERAGEIF function returns the #DIV0! error value if no cells in range meet criteria.
- AVERAGEIF function treats an empty cell in criteria as 0 (zero) value.
- AVERAGEIF function allows the wildcard characters asterisk (*) and question mark (?) in criteria. An asterisk wildcard for any character sequence, and a question mark wildcard for any single character; use a tilde (~) before character to find actual asterisk and question mark.
- No need for average_range and range argument to have the same size. Excel uses the top left cell in average_range as the starting point, and the corresponding cells in the range are averaged.
How to Use AVERAGEIF Function in Excel
For example, there is data as shown below. How to create excel formulas for the following questions:
Question #1. What are the average sales for all iPhone variants in the Middle area?
The Formula
=AVERAGEIF(A2:A14,"MIDDLE",D2:D14)
Criteria argument is case insensitive; you can use uppercase or lowercase as you like. The result is 6,692.25.
Question #2. What are the average sales of iPhone 7 in all areas?
The Formula
=AVERAGEIF(B2:B14,"iphone 7",D2:D14)
If you are looking for a sales average for an iPhone variant point the range argument to column B. The result is 7,484.67.
Question #3. What are the average iPhone sales for the “plus” variant in all areas?
The Formula
=AVERAGEIF(B2:B14,"*plus",D2:D14)
The wildcard in the formula above will count all iPhone variants that have the “plus” word. The result is 7,736.67.
Question #4. What are the average iPhone sales for the “plus” variant in the “East” area?
Not applicable. Why? There are two criteria, criteria #1 have the “plus” word, criteria #2 East area. AVERAGEIF function is only able to handle one criteria. For more than one criteria, please use Excel AVERAGEIFS function.
There is a trick that can make you able to use the AVERAGEIF function even with more than one criteria. Read the AVERAGEIF limitation below.
Question #5. What are the average sales for an iPhone with 128GB of storage?
The Formula
=AVERAGEIF(C2:C14,128,D2:D14)
If the criteria is a number, there is no need to add an equal sign in front of the number. The result is 6,006.00.
Question #6. What is the average sales for iPhones with storage of less than 128GB?
The Formula
=AVERAGEIF(C2:C14,"<128",D2:D14)
If you use less-than or greater-than sign, the criteria must be enclosed with quotes even it’s a number. The result is 8,045.38
Question #7. What are the average sales for an iPhone with storage of less than 256GB and more than 32GB?
Not applicable. Just like question #4, there are two criteria use the Excel AVERAGEIFS function to solve this question. If you still want to use the AVERAGEIF function, read the solution in the “AVERAGEIF limitation” below.
**
Please see the image below for the complete results of the AVERAGEIF function in column G
AVERAGEIF Limitations
The AVERAGEIF function limitation is unable to handle more than one criteria. There are two solutions to solve it.
The first solution is to use the AVERAGEIFS function. The second solution is using a helper column that contains logical function AND/OR. Use the result in helper column as AVERAGEIF range argument and specify the criteria to be TRUE.
For more details, read the following article for a reference. It’s a different function, but you can use the same trick to solve the AVERAGEIF function limitations.
