Excel WhisperExcel Whisper

How to Use AVERAGEIF Function in Excel

Complete guide to Excel's powerful conditional average function with practical examples

AVERAGEIF is a versatile Excel function that calculates the average (arithmetic mean) of values that meet specific criteria. Whether you need to find the average sales for a particular region, calculate average test scores above a certain threshold, or analyze performance metrics for specific categories, AVERAGEIF provides a straightforward solution. Learn through practical examples from basic conditional averages to more advanced filtering techniques.

Try AVERAGEIF Online

Test AVERAGEIF function directly in your browser - faster and easier than Excel

Pro Mode
Try this

Drop Excel or CSV files here

Upload up to 2 files (10MB total)โ€ข
Free Plan
Upgrade to Standard for higher limits โ†’

AVERAGEIF Function Guide

Step-by-step instructions for using AVERAGEIF

Function Syntax

=AVERAGEIF(range, criteria, [average_range])

Steps to Use

1

Select the cell where you want the average result

2

Start with =AVERAGEIF(

3

Select the range to check against your criteria (e.g., A2:A20)

4

Enter your criteria (e.g., "East" or ">50")

5

Optional: Select the range containing values to average if different from the first range (e.g., B2:B20)

6

Close with a parenthesis and press Enter

Use Cases

Regional Analysis

Calculate average metrics for specific regions or territories

Performance Evaluation

Find average scores or ratings that meet certain thresholds

Inventory Management

Determine average stock levels for specific product categories

Tips & Notes

  • 1If average_range is omitted, the function averages the values in range
  • 2Text criteria must be enclosed in double quotes (e.g., "East")
  • 3Numeric criteria can use comparison operators (e.g., ">50", "<=100")
  • 4Date criteria should use the proper format (e.g., ">="&DATE(2023,1,1))
  • 5You can use wildcards (* for any sequence of characters, ? for any single character) in text criteria
  • 6The range and average_range must be the same size
  • 7AVERAGEIF ignores text values and logical values in the average_range
  • 8For multiple criteria, use the AVERAGEIFS function instead

Frequently Asked Questions about AVERAGEIF

Common questions and solutions for Excel AVERAGEIF function

AVERAGEIF allows you to calculate an average based on a single criterion, while AVERAGEIFS lets you apply multiple criteria. AVERAGEIFS is more powerful for complex calculations where you need to meet several conditions simultaneously, such as finding the average sales for a specific region, product category, and time period.

The #DIV/0! error occurs when no cells meet your criteria, resulting in an attempt to divide by zero. To avoid this, you can wrap your AVERAGEIF function in an IFERROR function: =IFERROR(AVERAGEIF(range, criteria, average_range), "No matching data"). This will display "No matching data" instead of the error.

AVERAGEIF ignores text values in the average_range when calculating the average. It only considers numeric values that meet your criteria. If your average_range contains text that looks like numbers, you may need to convert them to actual numbers using the VALUE function before using AVERAGEIF.

For date criteria, use comparison operators with the DATE function: =AVERAGEIF(date_range, ">="&DATE(2023,1,1), value_range). This calculates the average of values where the corresponding dates are on or after January 1, 2023. Make sure your date criteria are formatted as dates or use the DATE function to create date values.

AVERAGEIF isn't designed to directly find the average of top N values. Instead, you can use a combination of LARGE and AVERAGE functions. For example, to find the average of the top 5 values in range A1:A20, use: =AVERAGE(LARGE(A1:A20,{1,2,3,4,5})). This returns the average of the 5 largest values in the range.

To exclude zeros when calculating an average, use ">0" as your criteria: =AVERAGEIF(range, ">0", average_range). To exclude blank cells, use "<>" as your criteria: =AVERAGEIF(range, "<>", average_range). If you need to exclude both zeros and blanks, you'll need to use AVERAGEIFS with two conditions.

Unexpected results often occur due to: 1) Hidden or filtered rows that are still included in the calculation, 2) Text values that look like numbers but aren't being included, 3) Formatting issues with dates or numbers, or 4) Criteria syntax errors. Check that your ranges are correct and that your criteria is properly formatted with quotes around text and operators.

AVERAGEIF is not case-sensitive by default. For case-sensitive averaging, you can create a helper column using the EXACT function to create TRUE/FALSE flags, then use AVERAGEIF to average only where the flags are TRUE. Alternatively, in newer Excel versions, you can use a combination of FILTER and AVERAGE functions for case-sensitive operations.