Conditional Aggregation
Average Salary by Department & Job Level (AVERAGEIF / AVERAGEIFS)
Calculate mean base compensation by department with AVERAGEIF and by department + seniority band with AVERAGEIFS, guarding zero-headcount #DIV/0! errors cleanly.
Compensation benchmarking without #DIV/0! crashes on unfilled department bands!
HR compensation tables routinely summarize mean salary by department (Engineering, Finance, Sales) and drill down by job level (Senior vs. Staff). Whenever a newly opened department or seniority band has 0 matching employees, raw AVERAGEIF and AVERAGEIFS divide 0 by 0 and throw #DIV/0!—breaking downstream summary averages and executive charts!
Guarding AVERAGEIF(B2:B7, F2, D2:D7) with COUNTIF(B2:B7, F2) = 0 and multi-criteria AVERAGEIFS(D2:D7, B2:B7, F2, C2:C7, G2) with COUNTIFS(B2:B7, F2, C2:C7, G2) = 0 keeps rounded compensation grids audit-clean while ignoring blank rows automatically!
| Compensation Filter | Matching Employees | Formula Used | Mean Base Salary |
|---|---|---|---|
| Engineering (All Levels) | 3 employees ($132k, $148k, $170k) | AVERAGEIF(B2:B7, "Engineering", D2:D7) | $150,000 |
| Engineering + Senior Only | 2 employees ($132k, $148k) | AVERAGEIFS(D2:D7, B2:B7, "Engineering", C2:C7, "Senior") | $140,000 |
| Legal (0 Active Headcount) | 0 employees (#DIV/0! Guard) | IF(COUNTIF(...)=0, "No Active Headcount", ...) | "No Active Headcount" |
Mean Salary by Department (Zero-Headcount Safe AVERAGEIF)
Computes the rounded average of salary column D2:D7 where department B2:B7 matches F2.
=IF(COUNTIF(B2:B7, F2) = 0, "No Active Headcount", ROUND(AVERAGEIF(B2:B7, F2, D2:D7), 0))Mean Salary by Department AND Seniority Band (AVERAGEIFS)
Filters salary D2:D7 simultaneously by department F2 and job band G2.
=IF(COUNTIFS(B2:B7, F2, C2:C7, G2) = 0, "No Active Headcount", ROUND(AVERAGEIFS(D2:D7, B2:B7, F2, C2:C7, G2), 0))Try the example
Use the original values or change them to test your understanding.