Statistical & Conditional Counting
Build a Dynamic Two-Way Count Matrix (Mixed-Reference COUNTIFS Cross-Tab)
Cross-tabulate ticket or order counts by Priority (rows) and Channel (columns) using mixed-reference COUNTIFS that updates instantly without manual PivotTable refreshes.
Mixed references ($E2 and F$1) turn one COUNTIFS into a full 2D matrix
PivotTables require manual refresh when source cells change, which breaks live operational dashboards. A mixed-reference COUNTIFS matrix updates on every keystroke and feeds stacked column or heatmap charts directly.
Locking the source columns ($B$2:$B$9 and $C$2:$C$9), locking column E on the row header ($E2), and locking row 1 on the column header (F$1) lets a single formula fill any R-by-C cross-tab grid plus a grand-total reconciliation check.
| Reference Token | Dollar Lock Type | Behavior When Copied Across & Down |
|---|---|---|
| $B$2:$B$9, $C$2:$C$9 | Full absolute | Always points to Priority & Channel columns |
| $E2 | Column-locked mixed | Tracks row label down rows 2..4, stays in col E |
| F$1 | Row-locked mixed | Tracks column header across cols F..G, stays in row 1 |
Two-Way COUNTIFS Cross-Tab Formula
Enter the formula once in F2, fill across columns F:G and down rows 2:4, and reconcile SUM(F2:G4) against COUNTA(A2:A9).
=COUNTIFS($B$2:$B$9, $E2, $C$2:$C$9, F$1)Multi-Step Dashboard Scaffolding & Live Review in Arte Sheets
In Arte Sheets, editors can ask the AI Assistant via text or voice to build the cross-tab headers, populate the mixed-reference COUNTIFS grid, and attach a stacked column chart in a bounded multi-step action (up to 10 steps per turn), while teammates co-editing in real time verify row totals and comment with @mentions.
=SUM(F2:G4) = COUNTA(A2:A9)Try the example
Use the original values or change them to test your understanding.