Lookup & Reference
Dynamic Category and Monthly Totals With Percent-of-Total (GROUPBY, PIVOTBY & PERCENTOF Patterns)
Build live category summaries, category-by-month cross-tab totals, and share-of-grand-total ratios using UNIQUE, SORT, SUMIF, and SUMIFS.
Cross-version dynamic summaries without GROUPBY / PIVOTBY lock-in
Excel's newer GROUPBY, PIVOTBY, and PERCENTOF functions condense category rollups, 2D monthly cross-tabs, and share-of-total calculations, but return #NAME? when workbooks are opened in older desktop builds.
Combining SORT(UNIQUE(A2:A8)) for distinct row headers, SUMIF for category totals, SUMIFS for Category x Month intersections, and SUMIF(...) / SUM($C$2:$C$8) for PERCENTOF share delivers live, zero-refresh reporting across Excel 2021+, Excel 365, and Arte Sheets.
| Category | 2026-01 Spend | 2026-02 Spend | Category Total | Share of Total (PERCENTOF) |
|---|---|---|---|---|
| Analytics | 500 | 400 | 900 | 0.15 (15%) |
| Cloud | 1,200 | 2,400 | 3,600 | 0.60 (60%) |
| Security | 800 | 700 | 1,500 | 0.25 (25%) |
| Grand Total | 2,500 | 3,500 | 6,000 | 1.00 (100%) |
Category Share of Grand Total (PERCENTOF Equivalent)
Divides the category subtotal for E2 by the grand total in C2:C8 with a zero-denominator guard.
=IF(SUM($C$2:$C$8) = 0, 0, ROUND(SUMIF($A$2:$A$8, E2, $C$2:$C$8) / SUM($C$2:$C$8), 4))2D Category-by-Month Cross-Tab Cell (PIVOTBY Equivalent)
Sums Amount in C2:C8 matching both Category (E2) and Month (F2).
=SUMIFS($C$2:$C$8, $A$2:$A$8, E2, $B$2:$B$8, F2)Try the example
Use the original values or change them to test your understanding.