How to Use AVERAGEIF in Excel
AVERAGEIF calculates the average of cells that meet one condition. Learn the syntax, practical budget examples, and when to use AVERAGEIFS instead.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
AVERAGEIF calculates an average only for rows that meet one condition. Use it when you need the average deal size for one region, the average expense for one category, or the average monthly revenue for a specific channel.
It is the average version of SUMIF: one range gets checked, and Excel averages the matching values.
The syntax
=AVERAGEIF(range, criteria, [average_range])- range — the cells Excel checks against the condition
- criteria — the condition to match
- [average_range] — the cells to average; if omitted, Excel averages the range itself
Step 1. Average expenses by category
Suppose column A contains expense categories and column B contains amounts. To calculate the average software expense:
=AVERAGEIF(A2:A100, "Software", B2:B100)This checks categories in A2:A100 and averages the matching amounts in B2:B100.
That pattern works well in an expense tracker when you want to understand whether one category is drifting higher over time.
Step 2. Average values over a threshold
If column B contains deal values, average only deals over $10,000:
=AVERAGEIF(B2:B100, ">10000")Because there is no separate average range, Excel averages the values in B2:B100 directly.
✦ TIP
">10000" must be wrapped in quotes.Step 3. Use a cell reference for criteria
If D2 contains the category to review, use:
=AVERAGEIF(A2:A100, D2, B2:B100)This makes the formula easier to reuse in a summary table. Change the category in D2 and the average updates automatically.
Step 4. Use AVERAGEIFS for multiple criteria
AVERAGEIF handles one condition. If you need category and month, use AVERAGEIFS:
=AVERAGEIFS(C2:C100, A2:A100, "Software", B2:B100, "May")In that formula, C2:C100 is the range being averaged. The rest of the arguments come in criteria-range and criteria pairs.
This is common in a small business budget when you want average spend by category within one month, quarter, or owner.
Common mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Mismatched range sizes | #VALUE! or wrong results | Keep range and average_range the same shape |
| Forgetting quotes around operators | Formula breaks | Use ">10000" |
| Averaging blanks accidentally | Result is lower than expected | Clean the source range first |
| Using AVERAGEIF for two criteria | Logic is incomplete | Use AVERAGEIFS |
The Griddy way
AVERAGEIF is simple until the sheet grows into multiple months, categories, owners, and exceptions.
"Calculate the average software expense for each month and ignore rows marked as one-time costs"
Griddy chooses the right average formula, applies it to the correct ranges, and keeps the summary tied to the source data.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Average the rows that matter in finance templates
AVERAGEIF is useful when budgets and expense logs need average amounts by category, month, owner, or business line without creating manual summary tables.
Expense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
Small Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.
Small Business Budget for Consultants
Plan consulting retainers, project revenue, subcontractors, software, travel, marketing, and operating margin in one budget spreadsheet.
Budget Tracker
Track income, expenses, and savings in one place. Line items, budgeted vs actual totals, and monthly net savings — free to use in your browser.