AVERAGEIFS function

AVERAGEIFS returns the average of values in a range that meet multiple criteria, enabling filtered averages across complex datasets.

=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

Generate a AVERAGEIFS formula

Describe what you need. The generator will reach for AVERAGEIFS where AVERAGEIFS is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How AVERAGEIFS reads its arguments
average_rangerequiredcriteria_range1requiredcriteria1requiredAVERAGEIFS →
ArgumentRequiredDescription
average_rangeRequiredThe range of cells to average; must contain numeric values. Empty cells and text are ignored; errors propagate.
criteria_range1RequiredThe first range to test for a condition; must have the same row count as average_range or results are unpredictable.
criteria1RequiredThe condition to apply to criteria_range1; can be a number, text, date, comparison operator (e.g., ">50" or ">="&DATE(2026,2,1)), or cell reference.
...RepeatingAdditional criteria_range / criteria pairs can be added as needed; all conditions must be true for a row to be included in the average.

Returns

A single numeric value representing the average of all cells in average_range where every criterion is true.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Average hours logged by a specific team member

TaskOwnerStart DateDue DateHours Logged
API AuthAlice2026-01-152026-01-2216
API AuthBob2026-01-152026-01-2212
DashboardAlice2026-01-202026-02-0324
DashboardCharlie2026-01-202026-02-0318
ReportingBob2026-02-012026-02-1520
ReportingCharlie2026-02-012026-02-1514
TestingAlice2026-02-102026-02-248
TestingBob2026-02-102026-02-2410
=AVERAGEIFS(E:E, B:B, "Alice")

Result: 16

AVERAGEIFS scans column B (Owner) for "Alice" and finds three matches (rows 1, 3, 7). It then averages the corresponding values from column E (16 + 24 + 8) ÷ 3 = 16 hours. This pattern scales to any column—just reference the owner column and the hours column.

2. Average hours for tasks due after a specific date

TaskOwnerStart DateDue DateHours Logged
API AuthAlice2026-01-152026-01-2216
API AuthBob2026-01-152026-01-2212
DashboardAlice2026-01-202026-02-0324
DashboardCharlie2026-01-202026-02-0318
ReportingBob2026-02-012026-02-1520
ReportingCharlie2026-02-012026-02-1514
TestingAlice2026-02-102026-02-248
TestingBob2026-02-102026-02-2410
=AVERAGEIFS(E:E, D:D, ">="&DATE(2026,2,1))

Result: 13

AVERAGEIFS evaluates column D (Due Date) against the criterion ≥ 2026-02-01 and finds four matching rows (5, 6, 7, 8). The average of their hours (20 + 14 + 8 + 10) ÷ 4 = 13. The DATE() function ensures the comparison works correctly across locales and date formats.

3. Average hours for a specific task assigned to a specific owner

TaskOwnerStart DateDue DateHours Logged
API AuthAlice2026-01-152026-01-2216
API AuthBob2026-01-152026-01-2212
DashboardAlice2026-01-202026-02-0324
DashboardCharlie2026-01-202026-02-0318
ReportingBob2026-02-012026-02-1520
ReportingCharlie2026-02-012026-02-1514
TestingAlice2026-02-102026-02-248
TestingBob2026-02-102026-02-2410
=AVERAGEIFS(E:E, A:A, "Dashboard", B:B, "Alice")

Result: 24

AVERAGEIFS applies two conditions: column A must equal "Dashboard" AND column B must equal "Alice". Only row 3 meets both conditions. The formula averages a single cell (24 hours), returning 24. This multi-criteria approach is the core strength of AVERAGEIFS—it scales to any number of conditions.

Common errors

Which AVERAGEIFS error are you seeing?
AVERAGEIFS returned an error#DIV/0!
Verify criteria are spelled correctly and match your data. Use COUNTIFS(range1, crit1, range2, crit2) first to confirm at least one row qualifies.
#VALUE!
Ensure average_range refers only to numeric columns. If it includes text or error cells, filter the range more narrowly (e.g., E2:E100 instead of E:E).
#REF!
Check all range references in the formula; re-enter deleted column references or fix sheet names (use 'Sheet1'!A:A syntax if the sheet name has spaces).
ErrorWhy it happensHow to fix it
#DIV/0!No rows match all criteria, so AVERAGEIFS has nothing to average and divides by zero.Verify criteria are spelled correctly and match your data. Use COUNTIFS(range1, crit1, range2, crit2) first to confirm at least one row qualifies.
#VALUE!The average_range contains text, errors, or mixed types that cannot be averaged.Ensure average_range refers only to numeric columns. If it includes text or error cells, filter the range more narrowly (e.g., E2:E100 instead of E:E).
#REF!A criteria_range or average_range references a deleted column, invalid sheet name, or broken cell reference.Check all range references in the formula; re-enter deleted column references or fix sheet names (use 'Sheet1'!A:A syntax if the sheet name has spaces).

Tips and when to use something else

  • Use COUNTIFS(range1, crit1, range2, crit2) beforehand to verify your criteria actually match rows—this prevents #DIV/0! surprises and speeds debugging.
  • For a single criterion, AVERAGEIF is simpler and more readable; reserve AVERAGEIFS for two or more criteria.
  • AVERAGEIFS ignores empty cells and text in average_range, so mixed data won't crash—but verify your data is clean to avoid wrong answers.
  • Combine AVERAGEIFS with MAXIFS or MINIFS to find not just the average, but the highest or lowest performers within the same filtered group.

Related functions

AVERAGEIFReturns the average of cells matching a single criterion, useful for conditional averages like mean cost by warehouse or mean inventory per SKU.COUNTIFSCOUNTIFS counts rows where all criteria are met across multiple ranges, useful for filtering inventory and finding matches across conditions.MAXIFSReturns the largest value in a range that meets multiple criteria, combining the power of MAX with IF-like filtering.SUMIFSSUMIFS returns the sum of cells that meet multiple criteria, making it ideal for aggregating data with several conditions at once.

Frequently asked questions

Can I use AVERAGEIFS with text criteria?
Yes. Text criteria work directly (e.g., criteria1 = "Alice"). Comparisons are case-insensitive in most spreadsheet applications. For wildcards, use "A*" to match any text starting with 'A'.
What's the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF averages based on one criterion (one range, one condition). AVERAGEIFS averages based on multiple criteria. If you need to average rows meeting two or more conditions, AVERAGEIFS is required.
Why does my AVERAGEIFS return #DIV/0!?
No rows match all your criteria. Check for typos, date format mismatches (use DATE() for safety), or case sensitivity issues. Run COUNTIFS with the same criteria to verify matches exist before averaging.
How do I average rows where a date falls within a range?
Use two criteria: one for the start date (>= DATE(2026,1,1)) and one for the end date (<= DATE(2026,2,28)). AVERAGEIFS requires both to be true, so only rows in that window are averaged.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17