Active-only dashboard, four formulas, two criteria each
The roster of 20 employees lives on the right (H-K) with Name, Department, Active? (Y/N), and Spend. HR's rule for the board pack: only ACTIVE employees count — terminated and on-leave rows stay in the roster but get filtered out of the dashboard. Four formulas on the left, each with TWO criteria (department AND active status). SUMIF/COUNTIF can't handle two criteria — this is where COUNTIFS and SUMIFS earn their keep.
Build the weekly department dashboard
Practice using SUMIFS to solve a real hr & operations problem. By the end, you’ll know when to reach for it and how to structure the arguments correctly.
The CFO wants a one-page department dashboard every Monday morning. The roster (Name / Department / Active? / Spend) lives in columns H:K — and HR's rule for the board pack is that only ACTIVE employees count: terminated and on-leave rows still sit in the roster but they're excluded from the dashboard. For each department, fill four columns on the left — active headcount, active total spend, average per active head, and a budget-status flag. Two criteria per formula (department AND active status) — the kind of build *IFS was made for.
Fill four formulas in row 2 and drag each down through row 4: C2 = COUNTIFS for Active Headcount (Department AND Active?="Y"), D2 = SUMIFS for Active Total Spend (same two criteria), E2 = SUMIFS / COUNTIFS for the per-active-head average, F2 = IFS comparing spend vs the budget cap (OVER if more than $5k above, UNDER if more than $5k below, otherwise AT). Lock the roster ranges with $ signs.
SUMIFS extends SUMIF to multiple conditions, all of which must be true for a row to be counted. You give it the sum range first, then pairs of criteria range and criterion. The standard way to answer 'how much did East region sell in Q3?' in one formula.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Try the formula first. Hints cost CP for a reason.