AVERAGEIF with One Condition in Excel and Google Sheets
Purpose
AVERAGEIF is the conditional average function — the companion to SUMIF and COUNTIF that most people discover a few months…
AVERAGEIF is the conditional average function — the companion to SUMIF and COUNTIF that most people discover a few months after they learn those two. Once you know it exists, you will use it constantly. Average sales for the North region only. Average test scores for students who received a grade of A. Average order value for orders above a certain threshold. Every time you want an average that applies to a subset of your data based on a condition in another column, AVERAGEIF is the answer. The structure is identical to SUMIF: the range you are checking, the condition to match, and the range you are averaging. Text conditions, comparison operators, wildcard patterns, and cell references all work exactly the same way as they do in SUMIF, so if you already know one you essentially know the other. For two or more conditions, step up to AVERAGEIFS which extends the same pattern.
Syntax
✓ Excel 2007+
✓ Google Sheets
= Same syntax
=AVERAGEIF(range, criteria, [average_range])
If average_range is omitted, the range argument is averaged directly.
Arguments
| Argument | Required | Description |
|---|---|---|
| range | Required | The column to check against the criteria — the condition column. |
| criteria | Required | The condition to match. Text like "North" in quotes, a number like 500, a comparison like ">500", or a cell reference like D1. |
| average_range | Optional | The column to average. If omitted, range itself is averaged. Must be the same size as range. |
How it works
AVERAGEIF scans the range argument and compares each cell to the criteria. For every matching row it records the corresponding value in average_range, sums them all, then divides by the count of matching rows. The average excludes non-matching rows entirely — it is not an average of zeros, it is an average of only the values that qualified. Blank cells and text in the average_range are ignored, so missing values do not deflate the average. If no rows match the criteria, AVERAGEIF returns #DIV/0! because it tries to divide by zero — wrap it in IFERROR to handle this cleanly in reports. The criteria argument follows exactly the same rules as SUMIF and COUNTIF: text must be in quotes, numbers do not need quotes, comparison operators go inside the quotes, and cell references are joined to operators using the & concatenation operator. When average_range is omitted, AVERAGEIF averages the values in range that match the criteria directly.
Examples
1
Calculate the average sales amount for the North region only from a regional sales dataset.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Region | Sales |
| 2 | Alice | North | 4200 |
| 3 | Bob | South | 3100 |
| 4 | Carol | East | 5500 |
| 5 | Dan | North | 3800 |
| 6 | Ella | South | 4900 |
| 7 | Finn | North | 5100 |
| 8 | Gia | East | 2800 |
| 9 | Hugo | North | 4700 |
| 10 | Iris | South | 3600 |
| 11 | Jo | East | 4100 |
| 12 | Average North | =AVERAGEIF(B2:B11,"North",C2:C11) |
Row 2: 4450
— Four reps are in the North region: Alice 4200, Dan 3800, Finn 5100, Hugo 4700. Their total is 17800 divided by 4 equals 4450.
The criteria North is in quotes because it is text. The range B2:B11 is the region column being checked, and C2:C11 is the sales column being averaged. Only the four North rows contribute to the result.
2
Average the exam scores for students who received a grade of A.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Grade | Score |
| 2 | Alice | A | 92 |
| 3 | Bob | B | 78 |
| 4 | Carol | A | 95 |
| 5 | Dan | C | 65 |
| 6 | Eve | A | 91 |
| 7 | Finn | B | 82 |
| 8 | Gia | A | 94 |
| 9 | Hugo | C | 71 |
| 10 | Iris | B | 86 |
| 11 | Jo | C | 68 |
| 12 | Average A | =AVERAGEIF(B2:B11,"A",C2:C11) |
Row 2: 93
— Four students received grade A: Alice 92, Carol 95, Eve 91, Gia 94. Total 372 divided by 4 equals 93.
Text matching is case-insensitive in AVERAGEIF, so A and a both match. The B and C grade rows are excluded entirely — the average is not pulled down by lower scores in other grade bands.
3
Average only the order amounts greater than 500, using a comparison operator as the criteria.
| A | B | |
|---|---|---|
| 1 | Order ID | Amount |
| 2 | ORD-001 | 350 |
| 3 | ORD-002 | 620 |
| 4 | ORD-003 | 1200 |
| 5 | ORD-004 | 480 |
| 6 | ORD-005 | 750 |
| 7 | ORD-006 | 290 |
| 8 | ORD-007 | 900 |
| 9 | ORD-008 | 410 |
| 10 | ORD-009 | 1100 |
| 11 | ORD-010 | 680 |
| 12 | Average >500 | =AVERAGEIF(B2:B11,">500") |
Row 2: 875
— Six amounts exceed 500: 620, 1200, 750, 900, 1100, and 680. Their total is 5,250 divided by 6 equals 875.
When average_range is omitted, AVERAGEIF averages the range column directly. The comparison operator ">500" goes inside quotes — writing >500 without quotes causes a syntax error. Orders below 500 (350, 480, 290, 410) are excluded from both the sum and the count.
Common use cases
1. Calculate average sales for a specific region from a multi-region sales log
=AVERAGEIF(B2:B100,"North",C2:C100)
2. Find the average score for students who passed a specific grade threshold
=AVERAGEIF(B2:B100,"A",C2:C100)
3. Average only orders above a minimum value to analyse high-value transaction behaviour
=AVERAGEIF(C2:C100,">500")
4. Use a cell reference as the criteria for an interactive average that updates when the cell changes
=AVERAGEIF(B2:B100,D1,C2:C100)
5. Average by department to compare team performance across multiple departments
=AVERAGEIF($B$2:$B$100,F2,$C$2:$C$100)
Common errors
#DIV/0! error
No rows match the criteria so AVERAGEIF is dividing by zero.
Fix: Wrap in IFERROR: =IFERROR(AVERAGEIF(B:B,"North",C:C),"No data") to show a friendly message when no rows match.
Result is wrong or includes unintended rows
The criteria text has a typo or extra space, or the range and average_range cover different row counts.
Fix: Check the criteria string matches the data exactly using COUNTIF first — if COUNTIF returns zero, the criteria is the problem. Ensure range and average_range are the same size.
#VALUE! error
The average_range contains text or mixed data types. AVERAGEIF ignores text cells in the average_range, but if the entire column is text the result may be unexpected.
Fix: Verify the average_range column contains numeric data. Use ISNUMBER to check individual cells if the result looks wrong.
Tips and variations
Use a cell reference for the criteria to build interactive dashboards
Put the condition value in a cell and reference it in AVERAGEIF. Change the cell and the average updates immediately — this is the pattern behind every single-condition summary table I build alongside SUMIF and COUNTIF.
=AVERAGEIF(B2:B100,D1,C2:C100)
Wrap in IFERROR to handle the no-match case cleanly
AVERAGEIF returns #DIV/0! when no rows match. IFERROR replaces that with a message or zero. For any report shared with stakeholders, always add IFERROR so unmatched categories show gracefully rather than with an error.
=IFERROR(AVERAGEIF(B:B,"North",C:C),"No data")
Use AVERAGEIFS when you need more than one condition
AVERAGEIF handles one condition. When you need the average for North AND the Sales team, switch to AVERAGEIFS with two range-criteria pairs. The syntax is a natural extension: =AVERAGEIFS(C:C,B:B,"North",D:D,"Sales").
=AVERAGEIFS(C2:C100,B2:B100,"North",D2:D100,"Sales")
Excel vs Google Sheets
Excel vs Google Sheets
AVERAGEIF works identically in Excel 2007 and newer and every version of Google Sheets. The syntax, criteria rules, and treatment of blank cells are the same in both applications. Formulas copy between them without changes.
Related reading
Frequently asked questions
AVERAGEIF calculates the average of cells in a range that meet a single condition. It works like SUMIF but divides the total by the count of matching cells rather than summing them. For example, =AVERAGEIF(B2:B100,"North",C2:C100) averages all values in column C where the corresponding cell in column B equals North.
AVERAGEIF handles one condition. AVERAGEIFS handles multiple conditions that must all be true simultaneously. If you want the average for North AND the Sales department, you need AVERAGEIFS. If you only need one condition such as just North, AVERAGEIF is simpler. AVERAGEIFS is available in Excel 2007 and newer.
Yes. The criteria argument supports comparison operators inside quotes. =AVERAGEIF(C2:C100,">500") averages only values greater than 500. You can use >, <, >=, <=, and <> just like COUNTIF and SUMIF. For a dynamic threshold from a cell, concatenate the operator: =AVERAGEIF(C:C,">"&D1).
AVERAGEIF returns a #DIV/0! error when no cells match because it tries to divide a sum by a count of zero. Wrap it in IFERROR to show a friendly message: =IFERROR(AVERAGEIF(B:B,"North",C:C),"No data"). This prevents the error from appearing in reports when a category has no matching rows.
Yes. AVERAGEIF ignores blank cells and cells containing text in the average_range when calculating the average. Only numeric values in matching rows are included in both the sum and the count. This means the average is not deflated by missing entries, which is usually the correct behaviour for conditional averaging.