Category totals
Sum Bank Transactions by Category in Excel
Reviewed 12 September 2026
Short answer
Short answer
Use a PivotTable for an interactive summary or SUMIFS for a fixed report. With Category in column F and Debit in column C, a category named in H2 can be totalled with =SUMIFS($C:$C,$F:$F,H2). Create a separate total for Credit so refunds, transfers and income are not silently netted against expenses.
Category totals become useful when the underlying table has consistent labels and genuine numeric values.
SUMIFS for a report, PivotTable for exploration
SUMIFS is simple when you already know which categories belong in the report. Microsoft defines it as summing a range when one or more criteria are met. Keep the sum range and category range the same size.
A PivotTable is better when you want to inspect every category, month or account without writing a formula for each one. Microsoft recommends a column-based source with a single header row, which is exactly how a transaction table should be structured.
Step by step
- 01
Convert the range to a table
Give each field one header and avoid blank rows inside the transaction data.
- 02
Check amount columns
Make sure Debit and Credit contain numbers rather than text with currency characters.
- 03
Choose the summary
Use SUMIFS for a stable report or Insert, PivotTable for flexible analysis.
- 04
Keep directions separate
Total debits and credits independently before deciding whether a net figure is meaningful.
- 05
Trace unusual totals
Filter back to the source transactions and correct categories rather than editing the total.
Checks before you rely on the spreadsheet
- Sum and criteria ranges have matching dimensions.
- Debit and Credit are not accidentally netted.
- Uncategorised rows have their own visible total.
- Totals trace back to source transactions.
Excel documentation used in this guide
Microsoft documentation checked 12 September 2026.
Questions people ask
Should I use SUMIF or SUMIFS?
SUMIFS is useful when you need more than one condition, such as category plus a date range or account. SUMIF is enough for a single condition.
Why does SUMIFS return zero?
Check that amounts are numeric, criteria text matches exactly and the sum and criteria ranges cover the same rows.
Can AussieBankStatements calculate category totals?
Yes. It produces rule-based categories and recalculates category totals when you edit them in the preview.