You need to find the average of numbers in Excel, but only for rows that meet a specific condition. Manually filtering and calculating is slow and error-prone. The AVERAGEIF function solves this by averaging cells based on a single criterion. This article explains how to use AVERAGEIF with clear, step-by-step instructions.
Key Takeaways: Using AVERAGEIF in Excel
- AVERAGEIF(range, criteria, [average_range]): Calculates the average of cells that meet a single condition you specify.
- Criteria with text or wildcards: Use quotes for text like “Completed” and asterisks (*) for partial matches like “*North”.
- Criteria with numbers or dates: Use operators like >10 or <>0 without quotes, or a cell reference containing the condition.
What the AVERAGEIF Function Does
The AVERAGEIF function is designed for conditional averaging. It examines a specified range for cells that match your criteria. It then averages the corresponding values from a separate range of numbers. You must have your data organized in a list or table format for the function to work correctly. The function ignores empty cells and text within the range being averaged.
The syntax has three arguments. The first is the range to check against your criteria. The second is the criteria itself, which can be a number, text, expression, or cell reference. The third, optional argument is the actual range of numbers to average. If you omit the third argument, Excel averages the cells in the first range you provided.
Understanding the Function Arguments
The range argument is the set of cells Excel evaluates with your criteria. These cells can contain numbers, text, or dates. The criteria argument defines which cells in the range are included. For example, "Apple" or ">100". The average_range argument is the set of numeric cells to actually average. It should be the same size and shape as the range argument. If average_range is left out, Excel uses the range argument for both checking criteria and calculating the average.
Steps to Use the AVERAGEIF Function
Follow these steps to build a formula with the AVERAGEIF function. Ensure your data does not have merged cells in the ranges you reference.
- Select the result cell
Click on the cell where you want the conditional average to appear. - Start the formula
Type an equals sign (=) followed by AVERAGEIF and an opening parenthesis. - Enter the range to check
Select the cells containing the values you will test with your criteria. For example, select a range of department names like B2:B100. Type a comma. - Enter the criteria
Type your condition. For text like “Sales”, enclose it in double quotes. For a number like 0, just type 0. For an expression like “>500”, enclose it in quotes. You can also click a cell that contains your criterion. Type a comma. - Enter the average range
Select the range of numeric cells you want to average, like C2:C100 for sales figures. This range must align with your first range. - Complete the formula
Type a closing parenthesis and press Enter. Excel calculates the average for only the rows that meet your condition.
Example with Different Criteria Types
Assume column A has regions and column B has sales values. To average sales for the “West” region, use: =AVERAGEIF(A2:A50, “West”, B2:B50). To average sales greater than $1000, use: =AVERAGEIF(B2:B50, “>1000”). Note the second formula omits the third argument, so it averages the same range it checks. To average sales not equal to zero, use: =AVERAGEIF(B2:B50, “<>0”).
Common Mistakes and Limitations to Avoid
#VALUE! Error with Mismatched Range Sizes
Excel returns a #VALUE! error if the range and average_range arguments are different sizes. For example, using A2:A10 for the criteria range and B2:B9 for the average range causes an error. Always ensure both selected ranges have the same number of rows and columns. Use your mouse to select both ranges in one action to prevent misalignment.
Incorrect Average Due to Hidden Rows
AVERAGEIF includes values from rows hidden by manual hiding or filtering. It does not ignore them. If you need an average based on filtered data, use the SUBTOTAL function with function number 101 instead. The formula would be =SUBTOTAL(101, B2:B100). This only averages visible cells in the range.
Criteria Not Working with Dates
When your criteria range contains dates, you must compare them correctly. Use the DATE function within the criteria. To average values for dates after March 15, 2024, use: =AVERAGEIF(A2:A100, “>”&DATE(2024,3,15), B2:B100). The ampersand (&) concatenates the operator with the serial number the DATE function creates.
AVERAGEIF vs AVERAGEIFS: Key Differences
| Item | AVERAGEIF | AVERAGEIFS |
|---|---|---|
| Number of criteria | One single condition | Multiple conditions (up to 127) |
| Argument order | average_range is the third, optional argument | average_range is the first, required argument |
| Use case | Simple filtering like “Region = East” | Complex filtering like “Region = East AND Sales > 1000” |
| Syntax example | =AVERAGEIF(range, criteria, [average_range]) | =AVERAGEIFS(average_range, criteria_range1, criteria1, …) |
You can now calculate averages in Excel based on specific text, numbers, or dates. Use AVERAGEIF for reports that need simple conditional analysis. For more complex rules, try the AVERAGEIFS function next. Remember that pressing F4 after selecting a range in the formula bar toggles absolute references, locking the range if you plan to copy the formula.