Excel sumifs
Baffled by what his money goes on, David Ludlow turned to Excel to track his spending
Bank statements are useful for checking what’s coming in and out of my account (it’s mostly the latter). But they are fairly blunt snapshots of spending over a certain month. I prefer to monitor my outgoings over the course of a year, to see which months are the most expensive. The smartest way to do that is to use Excel’s sumifs formula, which adds up values only if multiple criteria are met. In my case, I use it to add up spending based on categories (mortgage, utility bills, etc) within a set month.
As I explain how I do this, we’ll be hopping between two screenshots – A (above) and B (below right). To start, I created an Excel spreadsheet with two Tabs: Table and Transactions. On the Table tab, I created an outline. Running vertically from A2 I entered my categories: Shopping, Petrol, Council Tax, Utilities, Entertaining, Mortgage and Misc ( in screenshot A).
Running horizontally from cell B1, I added the month and year I want to track using the MMM-YY format: Sep-21 to Dec-21 . I’ll add Jan-22 onwards when the data is available. I downloaded the data from my bank (HSBC) as a CSV file. I then opened this and copied and pasted the data (this includes transaction dates, descriptions and values) into my spreadsheet’s Transactions tab after selecting cell B1. I’ll add (by pasting) future transactions to the bottom of this list.
To add categories, I clicked A to select all cells in that column, then clicked Data, followed by Data Validation. I then selected List in the Allow dropdown menu ( in screenshot B), then clicked the arrow to the right of the Source box . I clicked the Table tab in my spreadsheet, then the A column, pressed Enter and clicked OK. This gave me a dropdown list so that I could quickly add a category to every transaction
Back in the Table tab, I selected cell B2 and entered the following formula to add up the figures: =Sumifs(transactions!$d:$d, Transactions!$a:$a, "="&Table!$a2, Transactions!$b:$b, ">="&DATE(YEAR(B$1), MONTH(B$1),1), Transactions!$b:$b, "<="&EOMONTH(TABLE!B$1, 0)) – copy and paste this from our Pastebin account at www.snipca.com/40895.
This formula says: add up all sums, provided they’re in the category specified, and the dates fall between the start and end dates of the specified month. Let’s break that down.
First, Transactions!$a:$a, "="&Table!$a2, looks at the A column on the Transaction sheet (category) and identifies any that match cell A2 on the main sheet (that’s my Shopping category). Next, Transactions!$b:$b, ">="&DATE(YEAR(B$1), MONTH(B$1),1) looks at the date column on the Transactions tab and finds any that are equal to or greater than the date in the next formula. This uses the DATE() formula, extracting the year and month from the table header (YEAR(), MONTH() from Sep-21), while the ‘1’ means the first day of that month. As I need transactions until the end of September, I finish with "<="&EOMONTH(TABLE!B$1, 0). This looks for dates less than or equal to the last day (EOMONTH) of the header (Sep-21).
The ‘$’ signs tell Excel not to change parts of cell references when I copy and paste them. I copied cell B2 into the blank spaces in the table and they updated automatically. I also used the Format Cells option under Format in the Home tab to change the cell types to currency.