Conditional averages
AVERAGEIFS function: average values matching multiple criteria
Calculate the mean turnaround time for rows matching both site and priority, ignore unrecorded blanks while keeping real zeros, and handle #DIV/0! when no rows match.
Lab samples filtered by site and priority
A testing lab records turnaround hours (column C) by Site (column A) and Priority (column B). Row 6 is an unfinished Main / Urgent sample whose hours cell is still blank.
| Site (A) | Priority (B) | Hours (C) |
|---|---|---|
| Main | Urgent | 4 |
| Main | Routine | 14 |
| Main | Urgent | 8 |
| Annex | Urgent | 6 |
| Main | Urgent |
Put average_range first, followed by criteria pairs
Unlike AVERAGEIF (where average_range comes last), AVERAGEIFS places the numeric range C2:C6 first, followed by each criteria_range and criteria pair. Rows 2 and 4 match Main + Urgent with numeric hours (4 and 8), while blank C6 is ignored, giving (4 + 8) / 2 = 6.
=AVERAGEIFS(C2:C6,A2:A6,E2,B2:B6,F2)Real zeros vs blanks and unmatched criteria (#DIV/0!)
If C6 is recorded as 0 hours (an immediate walk-in check) rather than left blank, three rows qualify and the average drops to (4 + 8 + 0) / 3 = 4.
When zero rows match—for instance querying Site "Mobile"—the denominator is zero and AVERAGEIFS returns #DIV/0!. Wrap with IFERROR when a dashboard needs a text notice instead.
=IFERROR(AVERAGEIFS(C2:C6,A2:A6,E2,B2:B6,F2),"No completed samples")Try the example
Use the original values or change them to test your understanding.