Conditional sums and counts
AVERAGEIFS function playground
Averages the values that meet several conditions.
The playground runs in your browser. Nothing you type is sent anywhere.
How AVERAGEIFS works
AVERAGEIFS keeps only the rows that pass every condition, then averages their values from average_range.
The argument order matches SUMIFS: the range to calculate comes first, then pairs of ranges and conditions.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | What it means |
|---|---|
average_rangerequired | The cells to average. It comes first. |
criteria_range1required | The first range to test. |
criteria1required | The condition for that range. |
criteria_range2, criteria2, ...optional | More conditions; all must be met. |
Examples
| Formula | What it does | Result |
|---|---|---|
=AVERAGEIFS(F2:F11,D2:D11,I1,B2:B11,"<>"&I2) | Average sale of the product in I1, outside the region in I2. | $3,000.00 |
=AVERAGEIFS(E2:E11,B2:B11,"East",A2:A11,">="&DATE(2026,2,1)) | Average units in East from February on. | 4.5 |
=AVERAGEIFS(F2:F11,C2:C11,"Ben",E2:E11,">1") | Ben's average sale when more than one unit was sold. | $875.00 |
Common mistakes
Argument orderaverage_range comes first, unlike AVERAGEIF.
No matchesIf no row passes every condition, the result is #DIV/0!.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.