How to Calculate Conditional Averages in Google Sheets Using AVERAGEIF and AVERAGEIFS

Calculating the arithmetic mean across data subsets is fundamental to reporting and quantitative analysis in Google Sheets. While the standard AVERAGE function computes the mean of an entire range, real-world business scenarios often require conditional filtering—such as calculating average deal sizes for a specific sales region, finding the mean support response time for critical tickets, or calculating average monthly revenue within a specific date range.

Google Sheets provides two native conditional averaging functions: AVERAGEIF (for evaluating a single condition) and AVERAGEIFS (for testing multiple conditions across multiple columns). This comprehensive guide explains the syntax, logic, criteria operators, and practical formulas needed to master conditional averages in Google Sheets.

Syntax and Argument Structure

Understanding the argument order is crucial, as AVERAGEIF and AVERAGEIFS position their target averaging ranges differently.

1. AVERAGEIF (Single Criterion)

=AVERAGEIF(criteria_range, criterion, [average_range])

• criteria_range: The range of cells tested against the condition.
• criterion: The condition (e.g., "Marketing", ">500", or a cell reference like D2).
• average_range: (Optional) The range containing the numeric values to average. If omitted, Google Sheets averages the values in criteria_range.

2. AVERAGEIFS (Multiple Criteria)

=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Important Difference: In AVERAGEIFS, the average_range is listed as the first argument, followed by matching pairs of criteria ranges and conditions. All ranges must possess identical row and column dimensions.

Method 1: Single Condition Averages with AVERAGEIF

Suppose you manage an IT equipment register where column A lists Department names (e.g., “Engineering”, “Sales”) and column B lists Equipment Costs.

Text-Based Matching

To calculate the average equipment expenditure for the “Engineering” department:

=AVERAGEIF(A2:A100, "Engineering", B2:B100)

To reference a department dynamically from a cell (e.g., D2):

=AVERAGEIF(A2:A100, D2, B2:B100)

Numeric Comparison Operators

To calculate the average of transactions in column B that exceed £1,000 (omitting the optional third argument):

=AVERAGEIF(B2:B100, ">1000")

When combining logical comparison operators with cell references, concatenate using an ampersand (&):

=AVERAGEIF(B2:B100, ">=" & D2)

Method 2: Multi-Criteria Averages with AVERAGEIFS (AND Logic)

AVERAGEIFS enforces AND logic: an individual row is included in the calculated average only when every listed condition evaluates to TRUE.

Averaging by Department and Order Status

Suppose column A contains Department, column B contains Order Status (“Approved”), and column C contains Order Cost:

=AVERAGEIFS(C2:C100, A2:A100, "Engineering", B2:B100, "Approved")

Averaging Between Two Date Ranges

Calculating quarterly metrics requires restricting rows between start and end dates. Assuming column A contains Order Dates and column B contains Sales Revenue:

=AVERAGEIFS(B2:B100, A2:A100, ">=" & DATE(2026, 1, 1), A2:A100, "<=" & DATE(2026, 3, 31))

If start and end dates are placed in cells E1 and E2 respectively:

=AVERAGEIFS(B2:B100, A2:A100, ">=" & E1, A2:A100, "<=" & E2)

Using Wildcards for Partial String Matching

Both AVERAGEIF and AVERAGEIFS support standard wildcard characters for evaluating text:

• Question Mark (?): Matches any single character.
• Asterisk (*): Matches any sequence of zero or more characters.

For example, to average costs for departments starting with “Tech” (matching “Technology”, “Technical Support”, etc.):

=AVERAGEIF(A2:A100, "Tech*", B2:B100)

Handling Errors and Empty Sets with IFERROR

If no rows meet the specified conditions, AVERAGEIF and AVERAGEIFS return a #DIV/0! error because the denominator (count of matching items) equals zero. To prevent unsightly spreadsheet errors and present a clean fallback value, wrap the formula with IFERROR:

=IFERROR(AVERAGEIFS(C2:C100, A2:A100, "Finance", B2:B100, "Approved"), 0)

Or return a descriptive notice:

=IFERROR(AVERAGEIFS(C2:C100, A2:A100, "Finance", B2:B100, "Approved"), "No matching data")

Troubleshooting Common Calculation Issues

• Mismatched Range Dimensions: In AVERAGEIFS, if average_range spans C2:C100 but criteria_range1 spans A2:A50, Google Sheets returns a #VALUE! error. Ensure all range arguments have identical row heights.
• Leading or Trailing Whitespace: If text criteria fail to match entries that appear identical, invisible spaces in the source data may prevent equality. Use the TRIM function or Data Cleanup tool to cleanse source ranges.
• Excluding Zero Values: If zero-value entries artificially depress your average, append an additional criterion to ignore zeros:

  =AVERAGEIFS(B2:B100, A2:A100, "Engineering", B2:B100, "<>0")