AVERAGEIF returns the average (arithmetic mean) of cells in a range that meet a specified condition. It lets you average only the values that match your criteria, such as a region, a product, or a minimum score.
=AVERAGEIF(range, criteria, [average_range])What does AVERAGEIF do?
AVERAGEIF is a statistical function that calculates the average of numbers in a range only when they meet a condition you set. Instead of averaging everything, you tell Excel which rows to include, and it ignores the rest. The result is a single number that reflects just the subset you care about.
This matters because most real data mixes categories, dates, and amounts. You often need the average sales for one region, the average score for one class, or the average price for one product. AVERAGEIF answers those questions in one formula, without sorting or filtering your table.
Imagine you manage a small sales team and your manager asks for the average order value from the North region last quarter. Your sheet has hundreds of rows across four regions. With AVERAGEIF you point at the Region column, specify North, and point at the Order Value column. The answer appears instantly and updates if the data changes.
When to use AVERAGEIF
- Average sales for a specific region, product, or salesperson.
- Average test scores for students in one class or above a threshold.
- Average order value for a single customer or date range.
- Average delivery time for one carrier or warehouse.
- Average expense amount for a department or category.
- Average ratings for products with a minimum number of reviews.
AVERAGEIF syntax
=AVERAGEIF(range, criteria, [average_range])| Argument | Required? | What to enter |
|---|---|---|
range | Required | The range of cells to check against the criteria. It can contain numbers, text, dates, or logical values. Example: B2:B10. |
criteria | Required | The condition that decides which cells to average. It can be a number, text, cell reference, or expression. Text and operators must be in quotes. Example: "North" or ">100". |
average_range | Optional | The actual cells to average. If omitted, Excel averages the cells in range. Example: C2:C10. |
What it returns: A number: the arithmetic mean of the cells in average_range that correspond to rows where range meets the criteria. If no cells meet the criteria, AVERAGEIF returns the #DIV/0! error.
AVERAGEIF examples
1 Average sales for one region
The goal is to find the average sales amount for the North region. The formula checks each cell in A2:A6 for the text North, then averages the matching values in B2:B6. Only the three North rows (1200, 1500, 1800) are included, and their sum is 4500. Dividing by 3 gives 1500.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 1500 | ||
| 3 | South | 900 | |||
| 4 | North | 1500 | |||
| 5 | East | 1100 | |||
| 6 | North | 1800 | |||
| 7 |
2 Average scores above a threshold
Here you want the average of scores that are greater than 70. The criteria ">70" is applied to the Score column itself, so the third argument is omitted. The formula averages only the values 85, 90, and 78. Their sum is 253, and dividing by 3 gives approximately 84.33.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | |||
| 2 | Ana | 85 | 84.33333333 | ||
| 3 | Ben | 62 | |||
| 4 | Cara | 90 | |||
| 5 | Dan | 55 | |||
| 6 | Eve | 78 | |||
| 7 |
3 Average order value for a specific customer
You need the average order amount for customer C-102. The formula looks through A2:A7 for the exact text C-102 and averages the corresponding amounts in C2:C7. The matching orders are 250, 300, and 200, which total 750. Divided by 3, the average is 250.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Order Date | Amount | |||
| 2 | C-101 | 2024-01-05 | 180 | 250 | ||
| 3 | C-102 | 2024-01-07 | 250 | |||
| 4 | C-103 | 2024-01-08 | 320 | |||
| 5 | C-102 | 2024-01-10 | 300 | |||
| 6 | C-101 | 2024-01-12 | 210 | |||
| 7 | C-102 | 2024-01-15 | 200 | |||
| 8 |
4 Average delivery time for one carrier
The goal is the average delivery days for carrier FastShip. The formula checks B2:B7 for FastShip and averages the matching days in D2:D7. The FastShip deliveries took 3, 4, and 2 days, totaling 9. The average is 3 days.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order ID | Carrier | Ship Date | Days | |||
| 2 | 1001 | FastShip | 2024-02-01 | 3 | 3 | ||
| 3 | 1002 | SlowCo | 2024-02-01 | 6 | |||
| 4 | 1003 | FastShip | 2024-02-02 | 4 | |||
| 5 | 1004 | QuickPost | 2024-02-02 | 5 | |||
| 6 | 1005 | FastShip | 2024-02-03 | 2 | |||
| 7 | 1006 | SlowCo | 2024-02-03 | 7 | |||
| 8 |
5 Average expense for a department
You want the average expense amount for the Marketing department. The formula scans B2:B8 for Marketing and averages the values in D2:D8. The Marketing expenses are 500, 750, and 600, which sum to 1850. Dividing by 3 gives approximately 616.67.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Department | Expense Date | Amount | |||
| 2 | Kim | Marketing | 2024-03-01 | 500 | 616.6666667 | ||
| 3 | Lee | Sales | 2024-03-02 | 400 | |||
| 4 | Mia | Marketing | 2024-03-03 | 750 | |||
| 5 | Noah | IT | 2024-03-04 | 300 | |||
| 6 | Omar | Marketing | 2024-03-05 | 600 | |||
| 7 | Pia | Sales | 2024-03-06 | 450 | |||
| 8 | Quinn | IT | 2024-03-07 | 350 | |||
| 9 |
Common AVERAGEIF errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #DIV/0! | No cells in the range meet the criteria, so there is nothing to average. | Check the spelling and format of your criteria, or wrap the formula in IFERROR to show a friendly message. |
| #VALUE! | The average_range contains text that cannot be averaged, or the criteria is malformed. | Make sure average_range contains only numbers or blanks, and put text criteria in quotes. |
| Wrong result (too high or too low) | The range and average_range are different sizes, so Excel aligns them incorrectly. | Use ranges of the same size and shape, for example both starting at row 2 and ending at row 10. |
| Criteria not matching | Text criteria are case-insensitive but extra spaces or different data types can prevent a match. | Trim spaces with TRIM or use wildcards like "*North*" for partial matches. |
Tips for using AVERAGEIF
- Use cell references for criteria, such as
=AVERAGEIF(A2:A10,D1,B2:B10), so you can change the condition without editing the formula. - Combine operators with numbers in quotes, for example
">=100"or"North". - Wildcards work in text criteria:
?matches one character and*matches any sequence. - If average_range is omitted, AVERAGEIF averages the same cells it tests, which is handy for threshold averages.
- Remember that AVERAGEIF ignores blank cells and text in the average range, but counts zero values.
- For multiple conditions, use AVERAGEIFS instead of nesting AVERAGEIF.
Frequently asked questions
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF handles one condition. AVERAGEIFS lets you apply two or more conditions across different ranges.
Can AVERAGEIF use wildcards?
Yes. Use ? for a single character and * for any number of characters in text criteria, such as "North*".
Why does AVERAGEIF return #DIV/0!?
It means no cells met your criteria. Verify the criteria spelling and that the range contains matching values.
Does AVERAGEIF work with dates?
Yes. Use a date criteria in quotes, such as ">=2024-01-01", or refer to a cell that contains a date.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. AVERAGEIF reference