How do I average values based on multiple conditions?

Use AVERAGEIFS, which accepts multiple criteria and range pairs. For example, =AVERAGEIFS(costs, warehouse, 'North', cost, '>20') averages costs where warehouse is 'North' AND cost exceeds 20. AVERAGEIF only supports a single criterion.

Why does my AVERAGEIF return #DIV/0!?

No cells match your criteria, so there is nothing to average. Check for spelling errors, case sensitivity (text is case-insensitive but spacing matters), and valid operators in numeric comparisons. Verify matches exist using =COUNTIF(range, criteria).

Can AVERAGEIF work with dates?

Yes. Use criteria like '>'&DATE(2025,1,1) to find dates after January 1, 2025, or use date functions in the criteria argument. Ensure dates are stored as date values, not text, for reliable results.

What is the difference between AVERAGEIF and AVERAGE?

AVERAGE calculates the mean of all values in a range unconditionally, while AVERAGEIF only averages cells matching a criterion. Use AVERAGEIF when you need conditional averaging based on values in another column.

AVERAGEIF function

Returns the average of cells matching a single criterion, useful for conditional averages like mean cost by warehouse or mean inventory per SKU.

=AVERAGEIF(range, criteria, [average_range])

Generate a AVERAGEIF formula

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

Arguments

How AVERAGEIF reads its arguments

Argument	Required	Description
range	Required	The range of cells to evaluate against the criterion; can include numbers, text, dates, or references to be checked for matches.
criteria	Required	The condition to match, such as '>100' for numeric comparisons or 'North' for text; text criteria are case-insensitive.
average_range	Optional	Optional; the cells to average (if omitted, range is averaged instead); must align dimensionally with range.

Returns

A number representing the average of cells in average_range that match the criterion.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Average cost of items stored in the North warehouse

SKU	Warehouse	On Hand	Reorder Point	Cost
SKU-001	North	150	100	25.5
SKU-002	North	80	50	15
SKU-001	South	200	100	25.5
SKU-002	South	45	50	15
SKU-003	North	120	75	32
SKU-003	South	95	75	32

=AVERAGEIF(B2:B7,"North",E2:E7)

Result: 24.17

The formula scans the Warehouse column (B2:B7) for 'North' and finds rows 2, 3, and 6. It then averages the corresponding costs: (25.50 + 15.00 + 32.00) / 3 = 24.17. Use this to compare average costs across warehouse locations.

2. Average on-hand inventory for SKU-001 across all warehouses

SKU	Warehouse	On Hand	Reorder Point	Cost
SKU-001	North	150	100	25.5
SKU-002	North	80	50	15
SKU-001	South	200	100	25.5
SKU-002	South	45	50	15
SKU-003	North	120	75	32
SKU-003	South	95	75	32

=AVERAGEIF(A2:A7,"SKU-001",C2:C7)

Result: 175

The formula finds all rows where column A equals 'SKU-001' (rows 2 and 4) and averages their on-hand quantities: (150 + 200) / 2 = 175. This reveals average stock levels per SKU across your warehouse network.

3. Average reorder point for items costing more than 20

SKU	Warehouse	On Hand	Reorder Point	Cost
SKU-001	North	150	100	25.5
SKU-002	North	80	50	15
SKU-001	South	200	100	25.5
SKU-002	South	45	50	15
SKU-003	North	120	75	32
SKU-003	South	95	75	32

=AVERAGEIF(E2:E7,">20",D2:D7)

Result: 91.67

The formula evaluates costs in column E for values greater than 20, matching rows 2, 4, and 6 (costs 25.50, 25.50, 32.00). It then averages their reorder points: (100 + 100 + 75) / 3 = 91.67. This identifies threshold levels for higher-value inventory.

Common errors

Which AVERAGEIF error are you seeing?

Error	Why it happens	How to fix it
#DIV/0!	No cells in the range match the criteria, leaving nothing to average. AVERAGEIF cannot compute an average with zero matches.	Verify your criteria correctly identifies at least one cell (check spelling, spacing, and case for text; verify numeric ranges contain matching values). Use =COUNTIF(range, criteria) to confirm matches exist.
#VALUE!	The criteria argument contains invalid syntax, such as an incomplete operator (e.g., '>' without a value) or mismatched or malformed quotes.	Ensure criteria is well-formed: numeric comparisons like '>100' or '<=50', text like '"North"', or wildcards like '"SKU-*"'. Every operator must have an operand.
#REF!	The range or average_range references cells, columns, or rows that no longer exist, or uses invalid range syntax.	Verify that all referenced ranges exist and use valid syntax (e.g., A2:A10, not A2:10). If you deleted rows or columns, update the formula accordingly. Use absolute references ($A$1:$A$10) to prevent breakage if rows are inserted.

Tips and when to use something else

For multiple criteria, use AVERAGEIFS instead—e.g., =AVERAGEIFS(E:E, B:B, 'North', E:E, '>20') averages costs where warehouse is 'North' AND cost exceeds 20.
AVERAGEIF ignores empty cells and text values in average_range; they do not count toward the average denominator.
Use wildcards in text criteria: '"S*"' matches any text starting with 'S', and '"?KU"' matches any 3-letter text ending with 'KU'.
If average_range is omitted, AVERAGEIF averages range itself—useful when criteria and values are in the same column (e.g., average all costs above 20).

Related functions

AVERAGEIFSAVERAGEIFS returns the average of values in a range that meet multiple criteria, enabling filtered averages across complex datasets.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.COUNTIFCounts cells in a range that meet a specific criterion, returning a number — use it to tally matching rows like employee counts by department.MAXIFSReturns the largest value in a range that meets multiple criteria, combining the power of MAX with IF-like filtering.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

How do I average values based on multiple conditions?: Use AVERAGEIFS, which accepts multiple criteria and range pairs. For example, =AVERAGEIFS(costs, warehouse, 'North', cost, '>20') averages costs where warehouse is 'North' AND cost exceeds 20. AVERAGEIF only supports a single criterion.
Why does my AVERAGEIF return #DIV/0!?: No cells match your criteria, so there is nothing to average. Check for spelling errors, case sensitivity (text is case-insensitive but spacing matters), and valid operators in numeric comparisons. Verify matches exist using =COUNTIF(range, criteria).
Can AVERAGEIF work with dates?: Yes. Use criteria like '>'&DATE(2025,1,1) to find dates after January 1, 2025, or use date functions in the criteria argument. Ensure dates are stored as date values, not text, for reliable results.
What is the difference between AVERAGEIF and AVERAGE?: AVERAGE calculates the mean of all values in a range unconditionally, while AVERAGEIF only averages cells matching a criterion. Use AVERAGEIF when you need conditional averaging based on values in another column.

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