AVERAGEIFS calculates an arithmetic mean for rows meeting all specified conditions. It accepts one criteria pair or several. The average range comes first, unlike AVERAGEIF’s argument order.
AVERAGEIFS function syntax
=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])- average_range: numeric cells to average.
- criteria_range1 and criterion1: the first range and condition to test.
- Additional pairs narrow the same matching set with AND logic.
- Use equally sized, aligned ranges. Zero amounts count; blank and text amounts are ignored.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Region | Status | Amount |
|---|---|---|
| East | Closed | 200 |
| East | Closed | 300 |
| East | Open | 500 |
| West | Closed | 900 |
| East | Closed | 100 |
Average rows matching two text labels
Enter this formula in A9. East and Closed match three rows with amounts 200, 300 and 100. Their sum of 600 divided by three gives 200.
=AVERAGEIFS(C2:C6,A2:A6,"East",B2:B6,"Closed")Result: 200.
Average values inside a numeric interval
This checks the amount column against both inclusive bounds. Amounts 200, 300, 500 and 100 qualify, so their average is 275. The amount 900 is excluded.
=AVERAGEIFS(C2:C6,C2:C6,">=100",C2:C6,"<=500")Result: 275.
Average transactions throughout a month
Enter the additional table at A20. Both May transactions qualify, including the May 31 noon timestamp. The June 1 transaction does not, so the result is 150.
| Date | Amount | Product |
|---|---|---|
| =DATE(2026,5,1) | 100 | Apple |
| =DATE(2026,5,31)+0.5 | 200 | Almond |
| =DATE(2026,6,1) | 600 | Banana |
=AVERAGEIFS(B21:B23,A21:A23,">="&DATE(2026,5,1),A21:A23,"<"&DATE(2026,6,1))Result: 150.
Use a wildcard with another condition
The product condition A* matches Apple and Almond. The numeric condition keeps amounts of at least 100, so both qualify and their average is 150.
=AVERAGEIFS(B21:B23,C21:C23,"A*",B21:B23,">=100")Result: 150.
Use FILTER for row-by-row conditions
Separate FILTER conditions keep East and Closed rows before AVERAGE processes their amounts. This reproduces 200 while allowing more flexible expressions, including comparisons between aligned columns.
=AVERAGE(FILTER(C2:C6,A2:A6="East",B2:B6="Closed"))Result: 200.
Understand an empty matching set
North has no qualifying numeric amounts, so AVERAGEIFS returns #DIV/0!. This is not a zero average. Investigate whether criteria, blanks or text explain the missing numeric observations.
=AVERAGEIFS(C2:C6,A2:A6,"North")Result: #DIV/0!.
Use one criterion when that is enough
AVERAGEIFS also accepts one criteria pair. All four East amounts average 275. AVERAGEIF can express the same task, but its average range is in a different position.
=AVERAGEIFS(C2:C6,A2:A6,"East")Result: 275.
Other Google Sheets articles you may also like