Conditional sums and counts
AVERAGEIF function playground
Averages the values that meet one condition.
The playground runs in your browser. Nothing you type is sent anywhere.
How AVERAGEIF works
AVERAGEIF picks out the rows that meet the condition and averages their values from average_range, ignoring blanks and text.
If no row matches, there is nothing to divide by and the result is #DIV/0!.
Syntax
=AVERAGEIF(range, criteria, [average_range])| Argument | What it means |
|---|---|
rangerequired | The cells to test. |
criteriarequired | The condition. |
average_rangeoptional | The cells to average. Leave it out to average the tested cells. |
Examples
| Formula | What it does | Result |
|---|---|---|
=AVERAGEIF(B2:B11,I1,F2:F11) | Average sale in the region typed in I1. | $966.67 |
=AVERAGEIF(F2:F11,">1000") | Average of the sales above 1,000. | $2,193.75 |
=AVERAGEIF(D2:D11,"Tablet",E2:E11) | Average units per tablet sale. | 3 |
=IFERROR(AVERAGEIF(B2:B11,"South",F2:F11),"No sales") | Handle a region with no sales. | No sales |
Common mistakes
#DIV/0!Means no row matched. Check the spelling of the criteria or wrap the formula in IFERROR.
ZerosA 0 in average_range is included and lowers the average.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.