# SUMIFS Spending by Category and Month in Excel

- Author: CheapBankConvert (https://cheapbankconvert.com)
- Published: 2026-10-17
- Category: Budgeting from your statements
- Canonical: https://cheapbankconvert.com/blog/sumifs-spending-by-category

> Use SUMIFS to total spending by category and month from statement rows, with the date range trick and a check that nothing is left out.
To total SUMIFS spending by category and month, point the formula at the amount column, then add two conditions: the category column equals the category name, and the date column falls between the first and last day of the month. One formula, copied across a grid of categories and months, builds the whole spending summary from statement rows.

## SUMIFS arguments for a spending-by-category total

A SUMIFS formula takes the range to add first, then pairs of a range to test and the condition it must meet. For spending by category, the amount column is the range to add, and the category column with a category name is the first pair. Microsoft's [SUMIFS function reference](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b) allows up to 127 such pairs.

Assume a transactions tab named Tx with dates in column A, descriptions in B, amounts in C and categories in D. Money out is stored as negative numbers. The total for groceries across every row is `=SUMIFS(Tx!C:C, Tx!D:D, "Groceries")`. That returns a negative figure, so put a minus sign in front, `=-SUMIFS(...)`, if you want spending shown as a positive amount on the summary.

The order is the part people get wrong. In the older SUMIF function the range to add comes last, and in SUMIFS it comes first. Microsoft's reference calls out this difference, so reorder the arguments when you turn a SUMIF formula into SUMIFS.

Every range must also be the same size. If the amount range is C2:C500 and the category range is D2:D480, the formula returns an error. Using whole columns, or an Excel table, avoids the problem.

## How do you limit SUMIFS to one month of statement dates?

Limit SUMIFS to one month by adding two date conditions: the date is on or after the first of the month, and on or before the last day. Put the first day of each month in a header cell, then use `">="&B$1` for the start and `"<="&EOMONTH(B$1,0)` for the end. EOMONTH returns the last day of that month.

The full formula for groceries in the month whose first day sits in B1 becomes `=-SUMIFS(Tx!C:C, Tx!D:D, $A2, Tx!A:A, ">="&B$1, Tx!A:A, "<="&EOMONTH(B$1,0))`, where A2 holds the category name. The ampersand joins the comparison sign to the date, which is how SUMIFS accepts a condition built from a cell.

This only works if the dates in the transactions tab are real dates, not text that looks like a date. Test one cell with `=ISNUMBER(A2)`. A real date returns TRUE. If it returns FALSE, the dates came in as text, and the month conditions will silently skip those rows.

Statement periods rarely match calendar months. A statement that runs from the 18th to the 17th spreads across two calendar months, and that is fine: SUMIFS sorts each row by its own date, not by the statement it came from.

## A category-by-month SUMIFS grid

A category-by-month grid lists categories down column A and the first day of each month across row 1, with one SUMIFS formula filling every cell between them. The dollar signs in `$A2` and `B$1` lock the column and the row, so you write the formula once and drag it across and down.

The grid below shows example figures for three months. The amounts are illustrative.

| Category (example) | Jul 2026 | Aug 2026 | Sep 2026 | Row total |
| --- | --- | --- | --- | --- |
| Groceries | 574.20 | 611.85 | 590.10 | 1,776.15 |
| Eating out | 188.40 | 241.65 | 205.00 | 635.05 |
| Utilities | 203.12 | 197.40 | 176.88 | 577.40 |
| Transport | 132.00 | 158.75 | 140.30 | 431.05 |
| Uncategorized | 0.00 | 42.99 | 0.00 | 42.99 |
| Month total | 1,097.72 | 1,252.64 | 1,112.28 | 3,462.64 |

Keep an "Uncategorized" row in the grid. It uses the condition `""` against the category column, which picks up rows nobody has labelled yet, so they stay visible instead of disappearing from every total.

From here the grid feeds the rest of the budget. [Charting spending by category in Excel](/blog/chart-spending-by-category-in-excel) turns these rows into a chart that shows which category moved, and [calculating average monthly expenses](/blog/calculate-average-monthly-expenses) shows how to average the row totals without letting one odd month skew them.

## Does every statement row land in a SUMIFS total?

Check that every statement row landed in a SUMIFS total by comparing the grid with the raw data. Add up all spending in the transactions tab for the same date range with a plain SUMIFS on dates only, then subtract the grid's grand total. The difference should be zero. Anything else is a row the grid missed.

The check formula is `=-SUMIFS(Tx!C:C, Tx!C:C, "<0", Tx!A:A, ">="&B$1, Tx!A:A, "<="&EOMONTH(D$1,0)) - E7`, where E7 is the grid's grand total and B1 to D1 hold the first and last months. Put it in a cell next to the grid and give it a red fill if it is not zero.

A non-zero result has four usual causes. A category name in the transactions tab does not match any row label in the grid, so its rows fall through. A date is stored as text. Or a category the grid leaves out on purpose, such as transfers between your own accounts, is still counted in the raw total; exclude it from the check with one more condition, `Tx!D:D, "<>Transfer"`. Refunds are the fourth: a positive refund row lowers its category in the grid but is skipped by the "<0" condition, so the check differs by the month's refunds. Fix the cause, not the total.

## SUMIFS pitfalls: text amounts and stray spaces in category names

SUMIFS returns zero or too little when amounts are stored as text or when category names carry hidden spaces. Text amounts are skipped from the sum without any warning. A category typed as "Groceries " with a trailing space does not match "Groceries", so those rows drop out of the grid.

Amounts arrive as text most often when they were copied out of a PDF. They usually sit left-aligned in the cell, and `=ISNUMBER(C2)` returns FALSE. Converting the column with Data, Text to Columns, or multiplying by 1 in a helper column, fixes it. Starting from a file where amounts are already numbers avoids the problem; [converting a PDF to XLSX for free](/blog/convert-pdf-to-xlsx-free) compares the ways to get there. CheapBankConvert, which has not opened yet, is built to produce an .xlsx with amounts stored as numbers, priced once per statement; [the homepage](/) has the early prices.

Hidden spaces are fixed with TRIM. Run `=TRIM(D2)` in a helper column and paste the results back as values. A data-validation list for the category column stops new spelling variants from creeping in next month.

Wildcards are the last trap. In SUMIFS conditions, an asterisk matches any run of characters and a question mark matches one character. A category literally named "Kids*" would match "Kids school" and "Kids clubs" too, so keep category names to plain words.