One cell, three aggregations, the whole department snapshot
The CFO wants the board pack to render each department on a single line: headcount, total spend, and a status flag. The roster of 20 employees sits on the right (E:G). You'll glue COUNTIFS, SUMIFS, and IFS together with the & operator so one cell tells the full story.
Build a one-line department snapshot
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.
CFO wants a single-cell summary per department for tomorrow's board pack. Format: "X staff · $Y spend · STATUS". The status is OVER if spend exceeds the department's budget cap by $5k or more, AT if within $5k either way, UNDER if more than $5k below. The roster is in E2:G21 (Name / Department / Spend).
In C2, build a single string like "8 staff · $372000 · OVER" for the department in A2. Use COUNTIFS for the headcount, SUMIFS for total spend (you'll call it inside IFS too), and IFS to flag OVER if spend is $5k+ above B2, UNDER if $5k+ below, otherwise AT. Lock the roster ranges with $ signs and drag down through C4.
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.