Free AVERAGEIF Formula Generator (2026) — Conditional Average
Returns the average of all cells in a range that meet a given criteria.
AVERAGEIF: Calculate Conditional Means in Excel & Sheets
The AVERAGEIF function returns the average (arithmetic mean) of cells that meet a specific condition. It's perfect for calculating average sales per region, average scores for passing students, or average revenue for a product category.
Common AVERAGEIF Use Cases
Average sales for a specific product:
=AVERAGEIF(A:A, "Widget X", B:B)Average values above a threshold:
=AVERAGEIF(B:B, ">500")Average dates in a specific month:
=AVERAGEIF(A:A, ">="&DATE(2026,6,1), B:B)AVERAGEIF vs AVERAGEIFS vs AVERAGE
- AVERAGE — Simple mean of all values. No filtering.
- AVERAGEIF — Mean of cells matching one condition. Great for single-category reports.
- AVERAGEIFS — Mean with multiple conditions. Use for complex filtering like "average orders in Q1 over $100 from California."
Watch Out: AVERAGEIF ignores blank cells and text in the average_range. If no cells match the criteria, it returns #DIV/0!. Wrap with IFERROR to handle this gracefully: =IFERROR(AVERAGEIF(...), 0).
Common Errors & Fixes
AVERAGEIF returns 0
Causes:- No cells match the criteria, so the average of no cells is 0.
- Data type mismatch between criteria and actual data.
- Blank or incorrectly formatted criteria_range or average_range.
Fixes:- Verify the criteria matches at least one cell in the range.
- Use TRIM() to remove hidden spaces and TEXT()/VALUE() to align types.
- Ensure average_range and range are the same size.
Frequently Asked Questions
What is the difference between AVERAGEIF and AVERAGE?
AVERAGE returns the mean of all values. AVERAGEIF returns the mean only of cells that meet a specific condition, like averaging sales only for region "East".
Can I use wildcards in AVERAGEIF?
Yes. Use * for multiple characters and ? for single characters, like AVERAGEIF(A:A,"*Corp",B:B) to average values for all companies ending in "Corp".
Does AVERAGEIF work in Google Sheets?
Yes, AVERAGEIF works identically in Google Sheets with the same syntax.
Want to become an Excel Pro?
Stop searching for formulas. Master Excel in 30 days with this top-rated course.
Learn More