How to Summarize Columns in Excel: From Transaction Dump to Account-by-Month Totals

How-to · Updated July 2026 · ~13 min read

Every accounting system exports the same shape: one row per transaction, a date column, an account column, an amount column, and three thousand rows of it. Nobody wants that. What the review meeting wants is seven rows and twelve columns — accounts down the side, months across the top, and totals that tie.

Getting from one to the other is the single most repeated task in an Excel-based finance function, and there are three genuinely different ways to summarize data in Excel: SUMIFS, a PivotTable, and the GROUPBY/PIVOTBY functions that arrived in Microsoft 365 in 2024. This post runs the same dataset through all three, so you can see exactly what each one costs you and what it buys.

The dataset

Here is the export, in columns A to D, 3,742 rows of it. The first eight rows:

And here is the destination — the same data, summarised by account and month (Q1 shown; the real thing runs to twelve columns):

Two things about that grid. It has to foot both ways — rows to 399,445 and columns to 399,445 — and 399,445 has to equal =SUM(D2:D4000) over the raw data. A summary that does not tie to its source is a rumour.

First, the five-second answers

If you only need one number from one column, do not build anything.

Select the column and read the status bar. Excel shows Average, Count and Sum at the bottom right; right-click the status bar to add Numerical Count, Min and Max. Nothing is written to the sheet.

AutoSum. Click the cell directly below the column and press Alt+=. Excel guesses the range above and writes =SUM(D2:D3742). Check the guess — a blank row inside the data makes it stop early, which is the classic way a total quietly excludes December.

SUM, and what it ignores. =SUM(D2:D4000) skips text, logicals and blanks rather than erroring — helpful, until the export delivered half the amounts as text and your total is 40% light. Compare =COUNT(D2:D4000) (numbers only) against =COUNTA(D2:D4000) (anything non-empty); if they disagree by more than your blank rows, some amounts are text and every method below will under-total.

Totals that respect a filter. =SUM() ignores filtering entirely, so a filtered view can show the whole-column total under a partial list. Use =SUBTOTAL(9,D2:D4000), which excludes filtered-out rows, or =SUBTOTAL(109,D2:D4000), which also excludes rows hidden by hand. If the column contains errors, =AGGREGATE(9,6,D2:D4000) sums it anyway — 9 is SUM, option 6 is "ignore errors".

Method 1: SUMIFS by account and month

SUMIFS is the accountant's default and, oddly, the one the popular "ways to summarise data" listicles leave out. It reads: sum this column, where this other column matches this, and so on for up to 127 condition pairs.

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

Build the grid

Put the summary to the right of the data, on the same sheet, so no cross-sheet references are needed while you build it.

1. In F2:F8, list your accounts once each — Payroll, Rent, Marketing, Software, Travel, Professional fees, Utilities. (To generate that list from the data rather than typing it, =UNIQUE(B2:B4000) in Microsoft 365, or Data > Advanced > Copy to another location > Unique records only in any version.)
2. In G1, type the first month as a real date: 01/01/2025.
3. In H1, put =EDATE(G1,1) and fill right to R1. That gives twelve live month-start dates that shift if you change G1.
4. Select G1:R1, press Ctrl+1 > Number > Custom, and enter mmm-yy. The cells still contain dates; they just read as Jan-25.
5. In G2, enter:

=SUMIFS($D$2:$D$4000,$B$2:$B$4000,$F2,$A$2:$A$4000,">="&G$1,$A$2:$A$4000,"<"&EOMONTH(G$1,0)+1)

1. Copy G2 and paste across to R2, then down to row 8.

The two reference tricks that make it fill correctly

$F2 locks the column and frees the row, so as the formula travels right it keeps pointing at the account name in column F. G$1 locks the row and frees the column, so as it travels down it keeps pointing at the month header in row 1. Everything else is fully absolute. Press F4 repeatedly while a reference is selected in the formula bar to cycle G1 → $G$1 → G$1 → $G1.

Get this wrong and the symptom is distinctive: the first row is right and everything below it is nonsense, or the first column is right and the rest are zeros.

Why the date criteria look like that

The month test is two conditions, not one: on or after the first of the month, and before the first of the next.

• ">="&G$1 — note the ampersand. The operator is text, the date is a cell, and the two have to be concatenated. Writing ">=G$1" inside the quotes tests against the literal string "G$1" and returns 0.
• "<"&EOMONTH(G$1,0)+1 — EOMONTH(date,0) returns the last day of that date's month, so adding 1 gives the first of the next. "<="&EOMONTH(G$1,0) looks equivalent and usually is, but it drops any transaction stamped with a time: 31/01/2025 14:32 is numerically greater than 31/01/2025, so <= excludes it while < the first of February catches it. ERP exports carry timestamps more often than you would expect.

The trap: MONTH() does not work inside SUMIFS

The formula everyone tries first is =SUMIFS($D$2:$D$4000,MONTH($A$2:$A$4000),1,...), and it returns #VALUE!. SUMIFS criteria ranges must be actual ranges — you cannot wrap a function around one. There are two honest ways round it:

A helper column. In E2, =TEXT(A2,"yyyy-mm"), filled down, gives 2025-01. Then match on it directly: =SUMIFS($D$2:$D$4000,$B$2:$B$4000,$F2,$E$2:$E$4000,G$1) with 2025-01 as the header. Simple, fast, and it sorts chronologically because the year leads.

SUMPRODUCT, which does accept functions applied to ranges: =SUMPRODUCT(($B$2:$B$4000=$F2)(MONTH($A$2:$A$4000)=1)(YEAR($A$2:$A$4000)=2025)*$D$2:$D$4000). It works, but it is slower over large ranges, and a single text entry in the amount column returns #VALUE! rather than silently under-summing. Use it when the criteria genuinely need calculation; use SUMIFS otherwise.

Three more things that make SUMIFS return 0

• Trailing spaces in the account column. SUMIFS ignores case but not whitespace, so "Payroll " never matches "Payroll". Count the offenders with =SUMPRODUCT(--(TRIM($B$2:$B$4000)<>$B$2:$B$4000)) — a clean column returns 0.
• Dates stored as text. Left-aligned by default, they fail every >= comparison. Data > Text to Columns > Next > Next > Column data format: Date (DMY) > Finish converts them in place.
• Wildcards in account names. SUMIFS treats and ? as wildcards in text criteria, so an account literally called "Misc" matches more than you meant. Escape with a tilde: "Misc~*".

Tie it out before you send it

Three cells, added once, that make the schedule trustworthy: =SUM(G2:R2) filled down column S for row totals, =SUM(G2:G8) filled across row 9 for column totals, and a tie-out cell reading =SUM(G2:R8)-SUM($D$2:$D$4000).

The tie-out must be exactly 0. If it is not, the usual cause is an account in the data that is missing from your F2:F8 list. Count how many rows that affects:

=SUMPRODUCT(--(COUNTIF($F$2:$F$8,$B$2:$B$4000)=0))

Any answer above zero means transactions are falling outside the grid. This is the same discipline that makes a variance analysis defensible, and the same reason a P&L built in Excel should always carry a check row.

Method 2: the PivotTable

Same result, no formulas, and drill-down for free.

1. Turn the data into a Table first. Click any cell in the range and press Ctrl+T, confirm "My table has headers", then in Table Design rename it tblGL. This is the step people skip, and it is why their pivot stops picking up new rows.
2. Insert > PivotTable > From Table/Range, destination New Worksheet, OK.
3. In the PivotTable Fields pane, drag Account to Rows, Date to Columns, Amount to Values.
4. Excel 2016 and later group date fields automatically into Years, Quarters and Months, adding those levels to the Columns box; drag out whichever you do not want. If it did not happen, right-click any date inside the pivot, choose Group, select Months and Years, OK. A "Cannot group that selection" message means the column holds text dates or blanks — fix the source, then refresh.
5. Format the values through the field, not the sheet. Click the Sum of Amount field > Value Field Settings > Number Format. Formatting the cells from the Home tab instead looks identical and vanishes the moment the pivot is refreshed or rearranged.
6. Design > Report Layout > Show in Tabular Form gives the flat account-down-the-side grid rather than the indented default, and Design > Grand Totals > On for Rows and Columns adds the footings. Refresh with Alt+F5, or Ctrl+Alt+F5 for every pivot in the workbook.

Two symptoms worth recognising

"Count of Amount" instead of "Sum of Amount". Excel defaults to Count when the value column contains any text or blank cells. It is a diagnosis, not just an annoyance — clean the column, then set it back with Value Field Settings > Sum.

Deleted accounts still in the filter dropdown. Right-click the pivot > PivotTable Options > Data tab > Number of items to retain per field > None, then refresh. More on keeping pivots current in refreshing a pivot table in Excel.

What a pivot gives you that SUMIFS does not: double-click any value cell and Excel writes a new sheet containing every transaction behind that number. In a review meeting, that is the difference between answering and coming back to them.

Method 3: GROUPBY and PIVOTBY

Availability first, because it saves an argument. GROUPBY and PIVOTBY are Microsoft 365 functions released in 2024. As of 2026 they are not in Excel 2021, 2019 or 2016 — those versions return #NAME?, since perpetual releases generally do not pick up functions added after they shipped. Microsoft does revise which functions land in which channel, so check the current function-availability page before you promise a colleague it will work. Where they do run, they replace a pivot with a single formula that never needs refreshing.

Totals by account, largest first, no grand total:

=GROUPBY(tblGL[Account],tblGL[Amount],SUM,,0,-2)

The arguments are row fields, values, function, field headers, total depth, sort order. Total depth 0 suppresses the grand total (1 is the default, 2 adds subtotals). Sort order -2 sorts descending by the second column — negative for descending, the number being which column to sort on.

The full account-by-month grid:

=PIVOTBY(tblGL[Account],TEXT(tblGL[Date],"yyyy-mm"),tblGL[Amount],SUM)

PIVOTBY has no date grouping of its own, which is why the column field is a calculated array. TEXT(tblGL[Date],"yyyy-mm") produces 2025-01, which sorts chronologically because the year leads. If you would rather have real dates you can number-format, use EOMONTH(tblGL[Date],0) and apply mmm-yy to the spilled row.

Filtering is an argument rather than a separate control. To restrict it to Q1, add a filter array as the seventh argument: (tblGL[Date]>=DATE(2025,1,1))*(tblGL[Date]<DATE(2025,4,1)), which multiplies two TRUE/FALSE arrays into the 1s and 0s the argument expects. You can also stack functions — HSTACK(SUM,COUNT) in the function argument returns a total and a transaction count side by side, a useful sanity column.

What you give up: no drill-down, no slicers, no manual row reordering, and a #SPILL! error if anything sits in the space the result needs. Against that, it recalculates instantly, and it is auditable as a formula rather than as buried pivot settings.

Which method when

In practice most finance workbooks use two: a PivotTable to investigate, SUMIFS to publish.

When the export itself is the problem

None of this survives a dirty source. If the file arrives with merged header rows, subtotals interleaved with detail, or a fresh CSV every month, do the cleaning once in Power Query rather than by hand every month — Data > Get Data, transform, load, and thereafter one Refresh All. If it is new to you, start with Power Query for beginners.

Two specifics recur. Debit and Credit as separate columns need collapsing into one signed amount first — add a column with =[@Debit]-[@Credit] and summarise that. And general ledger exports tend to carry the account name on a header row with the transactions unlabelled beneath it, which has to be flattened before an account column exists to group on; the QuickBooks version of that job is covered in getting a QuickBooks general ledger into Excel.

How HISAB 360 helps

The mechanics above are learnable in an afternoon; the cost is that you rebuild them every time the source layout changes. HISAB 360 is a paid Excel add-in with an AI chat panel docked inside Excel that reads the open workbook and writes the summary itself. Ask it for "totals by account and month from the transactions sheet, twelve columns, with a tie-out to the source total" and it writes the SUMIFS grid — mixed references, month boundaries, check row — into the sheet. Ask for a PivotTable instead and it builds one; ask for Power Query and it writes the M.

It also removes a step further upstream. It connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting, pulling invoices, bills and GL activity into a sheet directly, so the transaction dump you are summarising is current and already column-shaped rather than a CSV somebody exported last Tuesday. Double-clicking a total in a generated summary drills through to the rows behind it, the way a PivotTable does.

Honest limits: it is Windows desktop Excel only — Microsoft 365 or 2016 and later, not Mac and not Excel on the web — and it is paid, with a 15-day trial that includes 50 AI credits and needs no card. And a generated schedule earns the same review as a junior's: check the tie-out cell, and spot-check two rows by hand.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.