How to average with multiple criteria in Excel (AVERAGEIFS)
Formula (Excel and Google Sheets)
=AVERAGEIFS(D2:D9,B2:B9,G1,C2:C9,">="&G2)Use AVERAGEIFS with the range to average first and one pair per condition: =AVERAGEIFS(D2:D9,B2:B9,G1,C2:C9,">="&G2).
- Result on the example
- $501,625 in
G3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.
How it works
AVERAGEIFS averages a row only when every condition is true: the neighborhood must be Linden and the bedrooms at least 3. Four homes qualify, and their prices average to $501,625. The two-bedroom Linden home is left out.
=AVERAGEIFS(D2:D9,B2:B9,G1,C2:C9,">="&G2)Result501625
D2:D98 rows × 1 col: $412,000, $298,000, $535,000, $345,000, …A range: 8 cells in one column.
B2:B98 rows × 1 col: "Linden", "Harbor", "Linden", "Linden", …A range: 8 cells in one column.
G1"Linden"The value in G1.
C2:C98 rows × 1 col: 3, 2, 4, 2, …A range: 8 cells in one column.
G23The value in G2.
">="&G2">=3"The & operator joins as text.
AVERAGEIFS(D2:D9,B2:B9,G1,C2:C9,">="&G2)501625AVERAGEIFS: Average of values that meet every condition.- average_range
D2:D98 rows × 1 col: $412,000, $298,000, $535,000, $345,000, … - criteria_range1
B2:B98 rows × 1 col: "Linden", "Harbor", "Linden", "Linden", … - criteria1
G1"Linden" - criteria_range2
C2:C98 rows × 1 col: 3, 2, 4, 2, … - criteria2
">="&G2">=3"
- average_range
In Google Sheets
The same formula works in Google Sheets: =AVERAGEIFS(D2:D9,B2:B9,G1,C2:C9,">="&G2). We ran it in Google Sheets mode on the same data and it returns $501,625.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =AVERAGE(FILTER(D2:D9,(B2:B9=G1)*(C2:C9>=G2))) | $501,625 | Excel 365 and Google Sheets: FILTER keeps the matching prices, and you can see them by removing AVERAGE. |
Common mistakes
- Every range must be the same size as the average range, or AVERAGEIFS returns #VALUE!.
- The comparison needs quotes:
">="&G2, not>=G2.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Average with multiple criteria in Excel (AVERAGEIFS)
G3, calculate the average price of homes in the neighborhood in G1 with at least the number of bedrooms in G2.Questions
How do I average between two values?
Use two conditions on the same column: =AVERAGEIFS(D2:D9,C2:C9,">=3",C2:C9,"<=4").
How do I use OR, like Linden or Harbor?
AVERAGEIFS can't; use FILTER with + : =AVERAGE(FILTER(D2:D9,(B2:B9="Linden")+(B2:B9="Harbor"))).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.