◆ Formulas I · drill · Spreadsheet Formulas & Data track
Sum on two criteria
SUMIFS takes the sum range FIRST — the reverse of SUMIF — and two mixed anchors fill the whole cross-tab from one formula.
The desk books revenue one line at a time — segment, region, amount — and the committee wants it as a segment by region cross-tab. A block is already framed beside the ledger, its segments down the side and its regions across the top, both in their own order. Roll the ledger into it, box the finished block, then total the cross-tab and prove it back against the ledger it came from.
The shortcuts in this drill
Alt walks: tap Alt, release, then the letters in sequence — each key steps one ribbon menu. On a Mac, tap ⌥ and use the same letters (KeyTips, Excel 2024+).
Alt+=⌥ = on macAutoSum the adjacent range
AutoSum reads the block above or beside the cursor and proposes the SUM — foot rows and columns without typing a formula.
Ctrl+D⌘D on macFill down from the cell above
Build the logic once in the top cell, select down, and stamp it — the standard way to fill a schedule.
Ctrl+R⌘R on macFill right from the cell to the left
Same as fill down, but across — one quarter’s formula becomes the whole year in two keystrokes.
Alt h b s⌥ h b s on macOutside borders
OUTSIDE border (S for outSide) — ONE frame around the selection’s perimeter, interior clean. The finished-table look; A is the grid of lines, S is the frame.
functions you'll type: SUMIFS() · SUM()
The optimal line
the par-setting sequence, straight through:
C4:C14 alt+= · F4 =SUMIFS($C$4:$C$13,$A$4:$A$13,$E4,$B$4:$B$13,F$3) · F4:F6 ctrl+d · F4:H6 ctrl+r · E3:H6 alt h b s · F8 =SUM(F4:H6) · F9 =F8-C14 · ctrl+s
More Formulas I drills