Math & Trig
Two-way summary matrix with SUMIFS and COUNTIFS (live cross-tab table)
Build a live PivotTable-style Region x Category grid that updates automatically without manual refresh using mixed references ($E2 and F$1).
Lock the row header column ($E2) and column header row (F$1)
A formula-driven summary recalculates when values in its referenced ranges change. Pivot refresh behavior depends on the tool and settings.
Use absolute source ranges and mixed row/column label references, then copy the formula across and down.
| Region \ Category | Hardware (F$1) | Software (G$1) | Row Total |
|---|---|---|---|
| North ($E2) | $1,450 (2 deals) | $900 (1 deal) | $2,350 |
| South ($E3) | $600 (1 deal) | $1,250 (2 deals) | $1,850 |
Mixed-reference two-way SUMIFS and COUNTIFS formulas
Lock $E2 on column E so dragging right keeps reading Region, and lock F$1 on row 1 so dragging down keeps reading Category.
=SUMIFS($C$2:$C$7, $A$2:$A$7, $E2, $B$2:$B$7, F$1)Two-way transaction count (COUNTIFS cross-tab)
Swap SUMIFS for COUNTIFS (omitting the sum range) to count matching orders per cell.
=COUNTIFS($A$2:$A$7, $E2, $B$2:$B$7, F$1)Inputs and interpretation
The bounded source range does not automatically include appended rows outside it. Use automatic recalculation and extend ranges deliberately. SUMIFS/COUNTIFS criteria are case-insensitive and interpret wildcard characters. Counts are transactions, not distinct orders. Enter the summary in F2 and copy across to G2 and down through row 3.
Try the example
Use the original values or change them to test your understanding.