AVERAGEIFS multiple criteria

Intermediate

In operations reporting, averages are often more useful when they’re filtered (for example: just high priority work, or just a specific quarter). AVERAGEIFS lets you calculate an average that matches multiple conditions at the same time.

AVERAGEIFS calculates the average of a range, but only for rows that meet all criteria you specify.

The syntax looks like:

=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2)

Your task:

  1. Review the tickets table in A1:E13. Response time is in the First response (min) column.
  2. Use the report inputs on the right:
    • Quarter is in G2
    • Priority is in G3
  3. In cell G4, calculate the average first response time for tickets where:
    • Quarter matches G2
    • Priority matches G3

Use AVERAGEIFS so the result updates if the report inputs change.

Need some help?

Hint 1

Your average range is the response time column (E). Your criteria ranges must cover the same ticket rows (2–13).

Hint 2

You need two criteria pairs: Quarter (column C) equals G2, and Priority (column D) equals G3.

Hint 3

If your result looks wrong, double-check that your ranges start on row 2 and end on row 13 (don’t include headers).

Related function(s)
AVERAGEIFSAVERAGEIFSUMIFSCOUNTIFS
More exercises
COUNTIFS multiple criteriaIntermediate
Dynamic date range SUMIFSIntermediate
SUMIFS multiple criteriaIntermediate
Year-over-year comparisonAdvanced
Back to exercises overview

Exercise