Written and maintained by CASRAI Editorial Board
Last updated
AVERAGEIFS in Excel returns the mean of the cells in one range that meet every condition you set on other ranges. The AVERAGEIFS function in Excel takes the range to average first, then pairs of a criteria range and a criterion. It is the function to reach for when you need a group mean, such as the average outcome for one site and one treatment arm, without filtering or copying data out of the sheet.
What AVERAGEIFS is used for in research
Research spreadsheets usually hold one row per participant, sample or record, with an outcome column and several descriptive columns (site, group, visit, status). AVERAGEIFS computes a conditional mean directly from that layout: average the outcome column only for rows where the site is “A” and the visit is “2”, for example. It complements the summary functions covered in the guide to Excel statistics functions, which lists AVERAGEIFS alongside COUNTIFS for conditional counts and averages.
Because every criterion must be true for a row to count, AVERAGEIFS combines conditions with AND logic only. It does not offer an OR option, and a row that fails any single condition is left out of the average entirely.
Install or access (official source only)
AVERAGEIFS is a built-in worksheet function in Microsoft Excel, so there is nothing to install. You type it into a cell like any other formula. The reference for the function is Microsoft’s own support page, AVERAGEIFS function (checked 2026-10-29), and the companion page AVERAGEIF function (checked 2026-10-29) documents the single-condition version.
Syntax and arguments
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- average_range (required): one or more cells to average, including numbers or names, arrays, or references that contain numbers.
- criteria_range1 (required) and further criteria_range2, … (optional): the ranges tested against your conditions. You can supply 1 to 127 ranges.
- criteria1 (required) and further criteria2, … (optional): the conditions, 1 to 127 of them. A criterion can be a number, an expression, a cell reference or text, for example 32, “32”, “>32”, “apples” or B4.
A cell in average_range is averaged only when all of its criteria are true. Each criteria range must be the same size and shape as average_range.
Wildcards in text criteria
In text criteria, a question mark matches any single character and an asterisk matches any sequence of characters. To find a literal question mark or asterisk, type a tilde (~) before it.
Step-by-step: average by two or three conditions
This worked example uses the sample data from Microsoft’s documentation: six listings with a price, a town, a bedroom count and a garage flag.
| Row | A: Type | B: Price | C: Town | D: Bedrooms | E: Garage? |
|---|---|---|---|---|---|
| 2 | Cozy Rambler | 230000 | Issaquah | 3 | No |
| 3 | Snug Bungalow | 197000 | Bellevue | 2 | Yes |
| 4 | Cool Cape Codder | 345678 | Bellevue | 4 | Yes |
| 5 | Splendid Split Level | 321900 | Issaquah | 2 | Yes |
| 6 | Exclusive Tudor | 450000 | Bellevue | 5 | Yes |
| 7 | Classy Colonial | 395000 | Bellevue | 4 | No |
- Decide which column holds the values to average. Here it is Price, so average_range is
B2:B7. - Pick the first condition and its range: Town equal to “Bellevue” gives
C2:C7, "Bellevue". - Add each further condition as another range and criterion pair, keeping every range the same size as B2:B7:
D2:D7, ">2"andE2:E7, "Yes". - Type the complete formula into an empty cell and press Enter:
=AVERAGEIFS(B2:B7, C2:C7, "Bellevue", D2:D7, ">2", E2:E7, "Yes")
Output and how to read it
The formula above returns 397839. You can check it by hand. Of the four Bellevue rows, row 3 fails the bedroom condition (2 is not greater than 2) and row 7 fails the garage condition (No). Only rows 4 and 6 meet all three conditions, and the mean of 345678 and 450000 is 397839.
A second check from the same documentation: =AVERAGEIFS(B2:B7, C2:C7, "Issaquah", D2:D7, "<=3", E2:E7, "No") returns 230000, because only row 2 is an Issaquah listing with three or fewer bedrooms and no garage. When the result looks wrong, recount which rows pass every condition and average those by hand.
Troubleshooting #DIV/0! and unexpected values
| What you see | Documented cause |
|---|---|
| #DIV/0! | average_range is blank or contains only text, its cells cannot be converted to numbers, or no cell meets all of the criteria. |
| An average that includes rows you expected to be excluded | If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value, so a criterion such as “0” can match blank cells. |
| TRUE and FALSE cells give a surprising mean | Cells in range that contain TRUE evaluate as 1, and cells that contain FALSE evaluate as 0 (zero). |
| Criteria ranges of a different size from average_range | Each criteria_range must be the same size and shape as average_range. Rewrite the ranges so they cover the same number of rows and columns. |
For instance, =AVERAGEIFS(B2:B7, C2:C7, "Bellevue", D2:D7, ">5") on the sample data returns #DIV/0!, because no Bellevue row has more than five bedrooms and so no cell meets all of the criteria.
AVERAGEIF vs AVERAGEIFS
The two functions differ in argument order and in the size rule, which is a common source of mistakes when converting one into the other.
| AVERAGEIF | AVERAGEIFS | |
|---|---|---|
| Syntax | AVERAGEIF(range, criteria, [average_range]) |
AVERAGEIFS(average_range, criteria_range1, criteria1, ...) |
| Number of conditions | One | 1 to 127 range and criterion pairs |
| Where the range to average goes | Last, and optional. If omitted, range is used. | First, and required. |
| Size of the averaged range | May differ from range; the top-left cell is the start and the size and shape of range are applied. | Each criteria_range must be the same size and shape as average_range. |
| TRUE and FALSE in the tested range | Ignored. | TRUE evaluates as 1 and FALSE as 0. |
Both functions return #DIV/0! when nothing meets the criteria. For other conditional summaries, see the guides on calculating the standard error of the mean in Excel and finding a slope in Excel, and the comparison of average versus median when deciding whether a mean is the right summary for your data.
Free alternatives
| Tool | Equivalent | Documented syntax |
|---|---|---|
| Google Sheets | AVERAGEIFS | AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]), same argument order as Excel (Google Docs Editors Help, checked 2026-10-29). |
FAQ
What does the AVERAGEIFS function in Excel do?
It averages the cells in average_range whose rows satisfy every criterion you supply. A cell is included only when all of its criteria are true.
Can AVERAGEIFS use OR logic?
No. Because a cell is averaged only when all of its criteria are true, the conditions are combined with AND. A single AVERAGEIFS formula cannot average rows that meet either of two conditions.
How many conditions can AVERAGEIFS take?
Microsoft documents 1 to 127 criteria ranges with 1 to 127 matching criteria.
Why does AVERAGEIFS return #DIV/0!?
Microsoft lists three situations: average_range is blank or text, its cells cannot be converted to numbers, or no cell meets all of the criteria. See the troubleshooting table above.
How do I match text with a partial value?
Use wildcards: a question mark matches any single character and an asterisk matches any sequence of characters. Type a tilde before a literal question mark or asterisk.
Sources
- Microsoft Support, AVERAGEIFS function, checked 2026-10-29.
- Microsoft Support, AVERAGEIF function, checked 2026-10-29.
- Google Docs Editors Help, AVERAGEIFS, checked 2026-10-29.
