Spreadsheets

PIVOTBY function: a category-by-month table from one formula

Build a category-by-month spending table with the PIVOTBY function, compare it with a normal pivot table, and use a SUMIFS fallback if you lack it.

Editorial illustration for PIVOTBY function: a category-by-month table from one formula

PIVOTBY builds a pivot-style table from one formula: =PIVOTBY(B2:B500,TEXT(A2:A500,"yyyy-mm"),C2:C500,SUM,0) puts categories down the side, months across the top and the total spent in each cell. It exists only in recent Microsoft 365 builds, so check by typing =PIVOTBY( in a cell. If Excel does not offer it, a SUMIFS grid (below) gives the same table.

How is the PIVOTBY formula written?

The arguments are, in order: the field for rows, the field for columns, the values, the function, and then optional settings. In this sheet, A holds dates, B categories and C amounts.

  • Rows: B2:B500, so each category gets a row.
  • Columns: TEXT(A2:A500,"yyyy-mm"), which turns each date into a label like 2025-04.
  • Values: C2:C500, the amounts.
  • Function: SUM, written without brackets. You could use COUNT, AVERAGE or MAX.
  • Field headers: 0, because the ranges do not include a header row. Use 1 or 3 if you select the header cells too.

The text labels sort in date order because year comes first, so Apr-25 does not jump ahead of Jan-26. PIVOTBY needs no month helper column, though adding one makes the formula shorter.

What does the output look like?

Take three categories and two months of made-up amounts:

Category2025-042025-05Total
Groceries9,20010,10019,300
Fuel3,4002,9006,300
Eating out2,6503,1005,750
Total15,25016,10031,350

Row and column totals come by default. To remove them, set row_total_depth and col_total_depth to 0, in positions 6 and 8 of the formula.

PIVOTBY or a classic pivot table?

PointPIVOTBYPivot table
RefreshUpdates instantly when data changesNeeds Refresh, unless a macro does it
Drag-and-dropNo, you edit the formulaYes, with a field list
Date groupingYou build the label with TEXTBuilt-in months, quarters and years
Where it worksRecent Microsoft 365 onlyAlmost every Excel version
FormattingPlain cells you style yourselfOwn style options

A pivot table is better when you want to explore by dragging fields around. PIVOTBY is better when the table must update by itself and sit inside a report with other formulas.

What if my Excel does not have PIVOTBY?

Use a SUMIFS grid. Put categories in A12 downward, month-start dates in B11 across (type 01-04-2025 in B11 and use =EDATE(B11,1) in C11, then fill right), and in B12 write:

  • =SUMIFS($C$2:$C$500,$B$2:$B$500,$A12,$A$2:$A$500,">="&B$11,$A$2:$A$500,"<"&EDATE(B$11,1))

Copy it across and down. It is more typing, but it works in every desktop Excel and in Google Sheets with the same logic. Format row 11 as mmm-yy.

Worked check: in the sample above, Groceries 9,200 + 10,100 gives 19,300, and the April column 9,200 + 3,400 + 2,650 gives 15,250. Always add up one row and one column of a result by hand like this, because a table that looks tidy can still be built on text amounts that quietly count as zero.

Why do I get #SPILL! or blank categories?

  • #SPILL!: something sits in the cells where the table wants to land. Clear them or move the formula.
  • A blank row in your range appears as an empty category. Trim the range to your data, or use an Excel Table such as Txn[Category] so it covers only filled rows.
  • Months showing as text codes: that is expected, since TEXT creates labels. Dates in the data must be real dates, not text.
  • Wrong totals: check that the amounts are numbers; a figure typed with a comma inside text counts as text.

When should I stop building tables by hand?

If you rebuild this table every month after pasting new statements, the formula is not the problem; cleaning the rows is. SpendixAI turns uploaded statements into categorised rows and has ready-made category and month reports, which you can download as CSV for your own sheet.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports