Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to average with a condition in Excel and Google Sheets
Topic:AVERAGE & central tendencyExcel & Google Sheets
=AVERAGEIF(A2:A6,"North",B2:B6)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Region | Sales |
| North | 80 |
| South | 50 |
| North | 90 |
| East | 60 |
| North | 70 |
=AVERAGEIF(A2:A6,"North",B2:B6)→80
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the output cell.
- 2Type =AVERAGEIF(.
- 3Enter the criteria range (the column to test), e.g. A2:A6.
- 4Enter the criterion, e.g. "North".
- 5Enter the average range (the column to average), e.g. B2:B6, then close the parenthesis and press Enter.
Tips
- If criteria range and average range are the same column, you can omit the third argument.
- Criteria can use comparison operators: =AVERAGEIF(B2:B6,">100") averages values over 100.
- For multiple conditions, use AVERAGEIFS: =AVERAGEIFS(C2:C6,A2:A6,"North",B2:B6,"Apple").
- AVERAGEIF is available in Excel 2007+ and all Google Sheets versions.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF supports one condition. AVERAGEIFS supports multiple conditions (AND logic) and has a different argument order: average_range comes first.
Can I use wildcards with AVERAGEIF?
Yes. =AVERAGEIF(A2:A6,"No*",B2:B6) averages values where column A starts with 'No'.
More on AVERAGE & central tendency
See all →How-to
How to calculate an average
Use the AVERAGE function to calculate the arithmetic mean of a range of numbers in Excel or Google Sheets.
How-toHow to average ignoring zeros
Use AVERAGEIF with a "<>0" criterion to calculate an average that excludes zero values from the calculation.
How-toHow to average ignoring blank cells
AVERAGE naturally ignores blank cells; use AVERAGEIF with "<>" to also ignore text placeholders like dashes.
How-toHow to calculate a moving average
Compute a rolling average for each row using AVERAGE with a fixed-window OFFSET or INDEX formula anchored to each row.
How-toHow to find the median of a range
Use the MEDIAN function to find the middle value in a dataset — the value that half the data falls above and half below.
How-toHow to find the most common value
Use MODE for numbers or COUNTIF-based logic for text to find the value that appears most frequently in a dataset.