SUMIFS by Month for Bank Transactions in Excel

TL;DR To total bank transactions by month in Excel, put your data in a table with a real date column, a signed Amount column and a Category column. Then use SUMIFS with two date conditions: on or after the first day of the month, and on or before the last day, which EOMONTH gives you. Add a category condition for category-by-month grids, and use ">0" or "<0" conditions on Amount to separate money in from money out. In Excel 365, UNIQUE and SEQUENCE build the month and category headers for you. Always add a check that the grid total equals the sum of the whole Amount column, so nothing falls through the gaps.

Monthly totals answer most of the questions people ask of their bank data. How much did we spend on software each month? Is income trending up? Which month was the big outlier? A pivot table can answer these, and our pivot table guide covers that route. But formulas have advantages: they update instantly, they sit exactly where you want them in a report, they're easy to reference from other sheets, and they make the logic visible.

This guide shows how to build monthly totals with SUMIFS step by step, from a single month to a full category-by-month grid, with formulas for both older Excel versions and Excel 365, plus Google Sheets equivalents. It includes a worked example and the mistakes that most often make totals wrong.

Set up the data first

SUMIFS is only as reliable as the data under it. You want a table like this:

Four requirements:

1. Real dates. Not text that looks like a date. Test with =ISNUMBER(A2); it should return TRUE for every row. If not, see fixing bank statement dates in Excel.
2. A single signed Amount column. Money in positive, money out negative. If your data has separate debit and credit columns, combine them first; see separating debit and credit columns for both directions.
3. Numbers, not text. =ISNUMBER(C2) should be TRUE. Remove currency symbols and thousands separators if not.
4. A Category column, if you want category totals. Our guide to Excel formulas for categorising transactions shows how to fill it automatically.

Convert the range to an Excel Table (select it and press Ctrl+T) and name it, for example Tx. Structured references like Tx[Amount] make formulas readable and grow automatically when you add rows.

If your data starts as PDF statements, convert them first. SheetStatement gives you one row per transaction with signed amounts and real dates, and checks each statement's balances. The free plan covers 3 pages a month; Starter ($9), Pro ($19) and Business ($39) monthly plans handle more.

The basic monthly SUMIFS

Put the first day of the month in a cell, say F2 contains 01/01/2026. Then:

=SUMIFS(Tx[Amount], Tx[Date], ">="&F2, Tx[Date], "<="&EOMONTH(F2,0))

How it works:

• ">="&F2 keeps rows on or after the first of the month.
• EOMONTH(F2,0) returns the last day of the same month, so "<="&EOMONTH(F2,0) keeps rows on or before it.
• SUMIFS adds the Amount for rows meeting both conditions.

This gives the net movement for the month: income minus spending.

Why not use MONTH(Tx[Date])=1? Because SUMIFS doesn't accept calculated arrays in its criteria ranges, and because matching on month number alone would combine January 2025 with January 2026. The date-range method avoids both problems.

Watch for times. If your dates include a time portion (common in some exports), a transaction at 3pm on 31 January is greater than 31/01/2026 00:00 and would be missed by "<="&EOMONTH(...). The safer version uses "less than the first of next month":

=SUMIFS(Tx[Amount], Tx[Date], ">="&F2, Tx[Date], "<"&EOMONTH(F2,0)+1)

Use this form by default; it works whether or not there are times.

Income and spending separately

Add a condition on Amount:

Income:   =SUMIFS(Tx[Amount], Tx[Date], ">="&F2, Tx[Date], "<"&EOMONTH(F2,0)+1, Tx[Amount], ">0")
Spending: =SUMIFS(Tx[Amount], Tx[Date], ">="&F2, Tx[Date], "<"&EOMONTH(F2,0)+1, Tx[Amount], "<0")

Spending comes out negative. If you want a positive figure in a report, wrap it in -( ... ) or ABS( ... ).

Be careful about transfers. Money moving from your current account to savings is negative but isn't spending; money coming back is positive but isn't income. If transfers are in your data, either give them a category like Transfer and exclude them:

=SUMIFS(Tx[Amount], Tx[Date], ">="&F2, Tx[Date], "<"&EOMONTH(F2,0)+1, Tx[Amount], "<0", Tx[Category], "<>Transfer")

or rely on category totals rather than the sign. In practice, category-based totals are more accurate than sign-based totals for any account with transfers, refunds or loan movements.

A category-by-month grid

This is the report most people actually want: categories down the side, months across the top.

Older Excel versions

Type the months across row 1 as first-of-month dates: 01/01/2026 in B1, then =EDATE(B1,1) in C1, filled right. Format them as mmm yyyy so they read "Jan 2026". Type the categories down column A starting in A2.

In B2:

=SUMIFS(Tx[Amount], Tx[Category], $A2, Tx[Date], ">="&B$1, Tx[Date], "<"&EOMONTH(B$1,0)+1)

The dollar signs matter: $A2 locks the category column, B$1 locks the month row. Fill the formula right and down.

Excel 365 with dynamic arrays

Let Excel build the headers.

Categories, sorted, in A2:

=SORT(UNIQUE(Tx[Category]))

Months across from B1, for twelve months starting with the earliest transaction month:

=EDATE(EOMONTH(MIN(Tx[Date]),-1)+1, SEQUENCE(1,12,0))

The inner part, EOMONTH(MIN(Tx[Date]),-1)+1, is the first day of the earliest month. SEQUENCE(1,12,0) produces 0 to 11 across a row, and EDATE adds that many months.

Then one formula in B2 fills the whole grid:

=SUMIFS(Tx[Amount], Tx[Category], A2#, Tx[Date], ">="&B1#, Tx[Date], "<"&EOMONTH(B1#,0)+1)

The # refers to the whole spilled range. Because A2# is a column and B1# is a row, SUMIFS returns a full two-dimensional grid. Add a category or a new month of data and everything updates.

Totals and the all-important check

Add a total row and total column:

Row total (per category): =SUM(B2:M2)
Column total (per month): =SUM(B2:B20)

Then add the check that catches almost every problem:

=ROUND(SUM(B2:M20) - SUMIFS(Tx[Amount], Tx[Date], ">="&B1, Tx[Date], "<"&EOMONTH(M1,0)+1), 2)

It compares the grid total with the total of all transactions in the same date range. It should be zero. If it isn't:

• Blank categories. Rows with no category don't appear in any category row. Add an (uncategorised) row with Tx[Category], "", or fill the blanks.
• Text dates or text amounts. SUMIFS ignores text. Check with ISNUMBER.
• Categories with trailing spaces. "Software " and "Software" are different to SUMIFS. Clean with TRIM.
• Dates outside the grid. Transactions before the first month or after the last aren't in the grid. Extend the range or accept the difference consciously.

Our bank statement data cleaning guide covers each of these fixes.

Worked example

A small, illustrative dataset. Figures are invented.

The category-by-month grid:

Check: the sum of all eleven amounts is 7,430.00, matching the grid total. Zero difference.

Now compare with the sign-based approach. "Spending" in February using "<0" would be −420.00 (hosting and ads), and "income" would be 2,240.00, because the ad refund counts as income. The category grid shows the truth: marketing for February was −260.00 net of the refund, and income was 2,200.00. In January, a sign-based spending total of −870.00 would include the 500.00 savings transfer, which isn't spending at all.

That's why a categorised grid is the better report for anything beyond a quick glance.

Averages, running totals and comparisons

Once the grid exists, other monthly measures are easy.

Average monthly spend per category:

=AVERAGE(B2:M2)

Be careful with months that have no activity yet, because they count as zeros. In Excel 365, =AVERAGE(FILTER(B2:M2, B$1:M$1<=TODAY())) restricts it to months that have started.

Year-to-date total in each month:

=SUM($B2:B2)

filled right gives a cumulative total.

Month-on-month change:

=C2-B2

or as a percentage, =IF(B2=0, "", C2/B2-1). Percentages on negative numbers can be confusing, so many people compare absolute spend: =ABS(C2)-ABS(B2).

Same month last year: if you have two years of data, put the prior-year month in a parallel grid using EDATE(B$1,-12) in the criteria.

A monthly cash summary that ties to your statements

A category grid tells you where money went. A cash summary tells you whether the months join up, and it's the report accountants, lenders and business partners tend to trust most because every figure can be checked against a bank statement.

Lay it out with months across the top and these rows:

The Difference row should be zero in every month. When it isn't, you know exactly which month has a missing or duplicated transaction, which narrows the search enormously. Our running balance guide shows how to pinpoint the exact row once you know the month.

This report only works for a single account at a time, because each account has its own balances. If you track several accounts, build one summary per account, or add an Account condition to each SUMIFS and stack the blocks.

It's also the backbone of a simple cash flow statement from bank statements, if you want to go a step further and group movements into operating, investing and financing activity.

Financial years and custom periods

Not everyone reports by calendar month. A UK tax year starts on 6 April, many companies have a financial year starting in a month other than January, and some businesses report in four-week periods.

Financial year totals. Put the year's start date in a cell and use EDATE(start,12) for the end:

=SUMIFS(Tx[Amount], Tx[Category], $A2, Tx[Date], ">="&$H$1, Tx[Date], "<"&EDATE($H$1,12))

Months within a financial year. Start the month headers at the first month of your year, for example 01/04/2026, and fill right with EDATE. Nothing else changes.

Periods that aren't months. For four-week or custom periods, list each period's start date in the header row and use the next header as the end:

=SUMIFS(Tx[Amount], Tx[Category], $A2, Tx[Date], ">="&B$1, Tx[Date], "<"&C$1)

You'll need one extra header cell at the end holding the date after the last period finishes.

Quarters. Use the first day of each quarter as the header and EDATE(B$1,3) as the end condition.

The principle is always the same: a start date included with ">=", an end date excluded with "<". Get that pair right and SUMIFS will total any period you like.

Using a month column instead

Some people prefer adding a helper column to the table:

Month =EOMONTH([@Date],-1)+1

This gives the first day of each transaction's month. Then SUMIFS becomes simpler:

=SUMIFS(Tx[Amount], Tx[Category], $A2, Tx[Month], B$1)

It's easier to read, and makes filtering and pivot tables simpler too. The downside is one extra column. Either approach is fine; pick the one your colleagues will understand.

Avoid a text month column like TEXT(A2,"mmm"). It loses the year and sorts alphabetically, so April comes before January.

Making the report easy to read

A grid full of negative numbers with two decimal places is accurate but tiring to read. A few presentation touches help, without changing any formula:

• Number format. A custom format such as #,##0;-#,##0;"–" shows whole numbers with thousands separators, negatives with a minus sign, and a dash instead of zero, so empty cells stop competing for attention.
• Order categories meaningfully. Put income first, then the largest cost categories, then transfers and anything uncategorised at the bottom. In Excel 365 you can sort the spilled category list by total with SORTBY.
• Highlight outliers. Conditional formatting with a colour scale per row makes an unusually expensive month stand out immediately.
• Freeze the headers. Freeze the first row and column so months and categories stay visible as you scroll.

Keep the check cell visible, ideally at the top of the sheet, so anyone reading the report can see it reconciles.

Google Sheets

SUMIFS, EOMONTH and EDATE work the same way in Google Sheets. Two differences:

• Sheets doesn't use Excel Table structured references. Use ranges like C2:C instead of Tx[Amount].
• For a fully spilling grid, combine ARRAYFORMULA or MAP and LAMBDA, or use QUERY. Our Google Sheets QUERY guide shows how to build category-by-month summaries with a single QUERY formula, which is often the easiest route in Sheets.

A single month total in Sheets:

=SUMIFS(C2:C, A2:A, ">="&F2, A2:A, "<"&EOMONTH(F2,0)+1)

Common mistakes

Using MONTH() and ignoring the year. Combines the same month across years. Use date ranges or a first-of-month helper column.

"<="&EOMONTH() with timestamps. Misses transactions on the last day after midnight. Use "<"&EOMONTH()+1.

Forgetting the dollar signs. The grid fills with wrong numbers when copied. Lock the category column and the month row.

Summing by sign with transfers in the data. Inflates both income and spending. Categorise transfers and exclude them, or use category totals.

No reconciliation check. Without it, uncategorised rows and text amounts silently disappear.

Combining accounts without care. If the table holds several accounts, a transfer between two of them appears twice, once out and once in. Over the combined data that nets to zero, which is fine, but per-account reports need an Account condition. See combining multiple bank statements.

Get clean data into Excel first

Monthly totals are easy once the data is clean. Upload your bank statement PDFs to SheetStatement for one row per transaction, real dates and signed amounts, checked against the statement balances. Try it free with 3 pages a month.

FAQ

How do I sum bank transactions by month in Excel?

Use SUMIFS with two date conditions: ">=" the first of the month and "<" the first of the next month (EOMONTH(date,0)+1). Add a category condition for category totals.

Why does my SUMIFS return zero?

Usually because dates or amounts are text, or the criteria are written as text without joining the cell, for example ">=F2" instead of ">="&F2.

Can SUMIFS use MONTH() as a condition?

Not directly on the range. Use date ranges, or add a helper column with the first day of each transaction's month.

How do I separate income and expenses?

Add ">0" or "<0" on the Amount column for a quick split, but category-based totals are more accurate if you have transfers or refunds.

How do I build a category-by-month table automatically?

In Excel 365, use SORT(UNIQUE()) for categories, EDATE with SEQUENCE for months, and a single SUMIFS referencing both spilled ranges.

Should I use a pivot table instead?

Pivot tables are great for exploring. SUMIFS grids are better for fixed reports and for referencing totals elsewhere. Many people use both.

Does this work in Google Sheets?

Yes. SUMIFS, EOMONTH and EDATE work the same; use ordinary ranges instead of Excel Table references, or try QUERY.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39.