Written and maintained by CASRAI Editorial Board
Last updated
AVERAGEIF returns the arithmetic mean of the cells in a range that meet one condition. In Excel the syntax is =AVERAGEIF(range, criteria, [average_range]). When you need more than one condition, AVERAGEIFS takes the same idea but reverses the argument order. This guide gives the syntax, the criteria patterns, worked examples you can recompute by hand, a cross-walk between the two functions, and a diagnosis table for the #DIV/0! error.
What AVERAGEIF is used for in research
A conditional mean answers a question such as “what is the average outcome for one group?” without sorting or filtering the sheet first. In a spreadsheet of study records you can average a score for one condition, one site or one category in a single cell, and the result updates when the data change. For wider function choices (mean, median, standard deviation, and which to use when), see Excel statistics functions: which to use and when.
The cell-by-cell logic is simple: AVERAGEIF tests each cell in range against criteria, adds up the matching cells in average_range (or in range itself if you leave that argument out), and divides by the number of matches.
Install or access (official source only)
Microsoft’s AVERAGEIF function page lists these applications: Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021, Excel 2019, Excel 2016 and Excel for the web. The function needs no add-in. Check the page for your version at the official source listed under Sources. LibreOffice Calc and Google Sheets also have conditional-average functions; see the table below.
Step-by-step: how to use the AVERAGEIF function in Excel
Syntax and arguments
AVERAGEIF(range, criteria, [average_range])
- range (required): the cells to test against the criteria. It can hold numbers, names, arrays or references that contain numbers.
- criteria (required): the condition, written as a number, an expression, a cell reference or text, for example
32,"32",">32","apples"orB4. - average_range (optional): the cells to average. If you omit it, Excel averages
range.
Steps
- Put your data in a sheet with the values to average in one column and the values to test in another (or the same) column.
- Click an empty cell for the result.
- Type
=AVERAGEIF(, then select or type the test range, for exampleA2:A5. - Type a comma and the criteria. Wrap operators and text in quotation marks, for example
"<250000". A cell reference such asB4needs no quotation marks. - If the cells to average are in a different column, type another comma and select that range, then close the bracket and press Enter.
Worked examples from Microsoft’s documentation
Microsoft’s example uses property values in A2:A5 (100000, 200000, 300000, 400000) and commissions in B2:B5 (7000, 14000, 21000, 28000). Every result below can be checked by hand.
| Formula | Result | How to check it |
|---|---|---|
=AVERAGEIF(B2:B5,"<23000") |
14000 | Commissions under 23000 are 7000, 14000 and 21000; their mean is 14000. |
=AVERAGEIF(A2:A5,"<250000") |
150000 | Values under 250000 are 100000 and 200000. |
=AVERAGEIF(A2:A5,"<95000") |
#DIV/0! | No value is under 95000, so there is nothing to average. |
=AVERAGEIF(A2:A5,">250000",B2:B5) |
24500 | Values above 250000 sit in rows 4 and 5; the matching commissions are 21000 and 28000. |
Wildcards and exclusion
In text criteria, ? matches any single character and * matches any sequence of characters. To match a literal question mark or asterisk, put a tilde (~) in front of it. Microsoft’s second example uses the regions East (45678), West (23789), North (-4789), South (New Office) (0) and MidWest (9678) in A2:B6:
=AVERAGEIF(A2:A6,"=*West",B2:B6)returns 16733.5, the mean of the West and MidWest values (23789 and 9678).=AVERAGEIF(A2:A6,"<>*(New Office)",B2:B6)returns 18589, the mean of every region except the one labelled “(New Office)” (45678, 23789, -4789 and 9678).
Illustrative research example
The following data are invented for illustration only. Column A (A2:A7) holds the group labels Control, Treatment, Control, Treatment, Control, Treatment, and column B (B2:B7) holds the scores 12, 15, 14, 18, 10, 17.
=AVERAGEIF(A2:A7,"Control",B2:B7)returns 12, because (12 + 14 + 10) / 3 = 12.=AVERAGEIF(A2:A7,"Treatment",B2:B7)returns about 16.67, because (15 + 18 + 17) / 3 = 16.67.
Pair a group mean with its spread before you report it; see how to calculate SEM in Excel and how to find sample variance.
AVERAGEIFS function in Excel: more than one condition
AVERAGEIFS averages the cells that meet all of several conditions. Its syntax is AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...), and it accepts up to 127 range and criteria pairs. A cell is averaged only if every one of its criteria is true.
AVERAGEIF versus AVERAGEIFS
| Point | AVERAGEIF | AVERAGEIFS |
|---|---|---|
| Number of conditions | One | One or more (up to 127 pairs) |
| Where the range to average goes | Last, and optional | First, and required |
| Size rule | The average range can differ in size from the test range; its top-left cell is the anchor and it is expanded to the size of the test range | Each criteria range must be the same size and shape as the average range |
Because the argument order flips, converting a formula by editing only the function name produces wrong results or errors. Rebuild the argument list instead.
Worked examples from Microsoft’s documentation
The house example has prices in B2:B7, towns in C2:C7, bedrooms in D2:D7 and garage (Yes or No) in E2:E7. Rows 2 to 7 hold: 230000 Issaquah 3 No; 197000 Bellevue 2 Yes; 345678 Bellevue 4 Yes; 321900 Issaquah 2 Yes; 450000 Bellevue 5 Yes; 395000 Bellevue 4 No.
=AVERAGEIFS(B2:B7,C2:C7,"Bellevue",D2:D7,">2",E2:E7,"Yes")returns 397839. Only rows 4 and 6 meet all three conditions (345678 and 450000), and their mean is 397839.=AVERAGEIFS(B2:B7,C2:C7,"Issaquah",D2:D7,"<=3",E2:E7,"No")returns 230000, because only row 2 qualifies.
Microsoft’s student example has Final scores in D2:D5 of 87, 88, “Incomplete” and 75. =AVERAGEIFS(D2:D5,D2:D5,"<>Incomplete",D2:D5,">80") returns 87.5, the mean of 87 and 88. In the same data set, First Quiz scores in B2:B5 are 75, 94, 86 and “Incomplete”. Averaging the scores above 70 and below 90 uses 75 and 86, so the result is 80.5. Recompute any result from the visible numbers like this before you trust it.
The same Example 2 data as an AVERAGEIFS formula
=AVERAGEIF(A2:A6,"=*West",B2:B6) can be written as =AVERAGEIFS(B2:B6,A2:A6,"=*West"). Both average the West and MidWest values. Note that the average range moves from last position to first.
Output and how to read it
The result is a single number: the arithmetic mean of the matching cells. Several cells are skipped silently rather than counted, which changes the denominator:
- In AVERAGEIF,
TRUEandFALSEvalues inrangeare ignored, and empty cells inaverage_rangeare ignored. - An empty cell in the criteria is treated as 0.
- In AVERAGEIFS,
TRUEcounts as 1 andFALSEcounts as 0 in the criteria ranges.
Note that a conditional mean says nothing about how many cells matched. To report a group mean, also count the matching cells (the COUNTIF function does this) so the reader can judge the sample size. Mean, median and spread are compared in descriptive versus inferential statistics.
Troubleshooting: #DIV/0! and other problems
| Situation | Result | What to do |
|---|---|---|
| No cell meets the criteria (AVERAGEIF or AVERAGEIFS) | #DIV/0! | Check the operator, the quotation marks and the spelling of text criteria. |
The AVERAGEIF range is blank or contains only text |
#DIV/0! | Point the function at the numeric column. |
| The AVERAGEIFS average range is blank or text, or its cells cannot be converted to numbers | #DIV/0! | Check that the values are stored as numbers. |
| AVERAGEIFS criteria ranges differ in size or shape from the average range | Not allowed by the function’s rules | Make every range the same size and shape. |
A literal ? or * is meant, not a wildcard |
Unexpected matches | Put ~ in front of the character. |
If a mean looks wrong but no error shows, test the criteria on a few rows by hand, as in the tables above. For missing values in analysis software, see handling missing values in SPSS.
Free alternatives
| Application | Conditional-average functions documented | Source |
|---|---|---|
| LibreOffice Calc | AVERAGEIF (one condition) and AVERAGEIFS (multiple criteria), both returning the arithmetic mean of cells that meet the condition | LibreOffice Help, Statistical Functions Part Four |
| Google Sheets | AVERAGEIFS, with the syntax AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]), the same argument order as Excel |
Google Docs Editors Help, AVERAGEIFS |
Frequently asked questions
How do I use the AVERAGEIF function in Excel?
Type =AVERAGEIF(range, criteria, average_range) in a cell: the range to test, the condition, and (if different) the cells to average. See the numbered steps and the worked examples above.
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF takes one condition and puts the optional average range last. AVERAGEIFS takes one or more conditions, puts the required average range first, and needs every range to be the same size and shape.
Why does AVERAGEIF return #DIV/0!?
Either no cell meets the criteria, or the range to test is blank or contains only text. The troubleshooting table lists each case.
Can AVERAGEIF use wildcards?
Yes. Use ? for a single character and * for any sequence of characters, and ~ to match a literal ? or *.
Does AVERAGEIF work with a different average range?
Yes. The average range need not match the test range in size; Excel anchors on its top-left cell and expands it to the size of the test range. With range A1:A5 and average range B1:B3, the cells B1:B5 are evaluated.
Sources
- Microsoft Support, AVERAGEIF function: support.microsoft.com/en-us/office/averageif-function-faec8e2e-0dec-4308-af69-f5576d8ac642 (checked 29 October 2026)
- Microsoft Support, AVERAGEIFS function: support.microsoft.com/en-us/office/averageifs-function-48910c45-1fc0-4389-a028-f7c5c3001690 (checked 29 October 2026)
- Google Docs Editors Help, AVERAGEIFS: support.google.com/docs/answer/3256534 (checked 29 October 2026)
- LibreOffice Help, Statistical Functions Part Four: help.libreoffice.org/latest/en-US/text/scalc/01/04060184.html (checked 29 October 2026)
