Hi all! I am a newbie at formulas in google sheets. I would like to have certain totals i bring into a self-designed sheet every month. If I literally just wanted to be able to calculate the total income for the month utilizing the transactions sheet, what is the simplest way to do so? I tried to click into cells on the other report sheets but they are very complicated and I’m sure have a lot of other code in there that i dont need. I know I can make a pivot table or use the category tracker to look at this, but I want to start off working with the data myself and the correct formula for this would allow me to customize it for other values i want to extract from the transaction sheet.
I know i could learn to do this from googling it but you guys are all wonderful and im sure would take far shorter to just ask. Thanks!!!
Queries are probably the best way to do this. If you’re not familiar with them, they can seem complicated, but hopefully starting with something that does what you want will help you understand how they work. Here’s what I’d use if I wanted to get all the income from my ‘Income-Paycheck’ category, where B is Date, D is Category, and E is Amount: =QUERY(Transactions!$B$4:$E,"SELECT SUM(E) WHERE D = 'Income-PayCheck' LABEL SUM(E) ''")
If you have multiple categories you want to pull from: =QUERY(Transactions!$B$4:$E,"SELECT SUM(E) WHERE D = 'Income-PayCheck' OR D = 'Income-Misc' LABEL SUM(E) ''")
If you wanted to limit that to just income from this year: =QUERY(Transactions!$B$4:$E,"SELECT SUM(E) WHERE D = 'Income-PayCheck' OR D = 'Income-Misc' AND Year(B) = 2021 LABEL SUM(E) ''")
And limiting it to just this month: =QUERY(Transactions!$B$4:$E,"SELECT SUM(E) WHERE D = 'Income-PayCheck' OR D = 'Income-Misc' AND Year(B) = 2021 AND Month(B)+1=" & MONTH(Today()) & " LABEL SUM(E) ''")
You could do a group instead of a category, or Category Type if that makes sense for what you’re looking for.
In the Category column, click the Filter, go to Filter by values section, click Clear, then select just the income categories you desire.
Then in the Date column, click the Filter, go to Filter by condition, choose Greater than and enter the following formula which always provides the last date of last month:
I do something similar but I use VLOOKUP from the Monthly Budget report. I added a column to pull Monthly Budget values for each category into my custom Spending Sheet using a formula similar to below (changing Home Insurance to the appropriate category on each row).
I do have to change the month on the Monthly Budget sheet each month. To make sure this has been done, I have the header of the VLOOKUP column pull the month selected on the Monthly Budget sheet so I can easily check that I changed it.
Would you mind telling me more about your custom Spending Sheet? I’m interested in trying the workflow you laid out for allison91.
My goal is sum up the spending in each category at the end of a quarter.
Thank you.
Sure! I made this starting in 2018 before Tiller had all of the cool reports it has now! Yearly Budget is similar, but I don’t envelope budget so my focus is more on actuals than making sure my spending is within a specific amount. (Paula Pant calls it the anti-budget where you save first and then spend the rest.)
Each year I create a spreadsheet of our spending plan so we can forecast when big bills will hit as well as general expenses. I color last year’s cost blue if it’s something we expect will change so I can have a quick reference without having to look back in time (a/c bill in July, for example!).
To make it quick to update the actuals for each month, I have a column to pull in Tiller’s Monthly Budget Values. I pick the month I want to see directly in the Monthly Budget and then flip back to my Spending Sheet to go category by category. I either copy from the lookup column (April in dark blue) and paste values only into the gray month columns or manually enter the amounts. (For accounts that don’t get pulled in magically it’s quicker to type in numbers here vs. adding a bunch of manual transactions.)
The column in dark blue that says April is a lookup column to Tiller’s Monthly Budget sheet with a code for each category row.
(A5 equals the Category to the left of the lookup column and is the only part of the formula that will change. Once it’s pasted in the first cell, you can drag it down and it should auto-populate.)
===
If you’re looking for something that sums your categories by quarter, you can generate a Category Rollup Report (Tiller Community Solutions / Tools / Reports). Once generated, it is a static report so you can’t do the lookup column as simply. This was the report I used to populate my spending sheet when I first created it and I would just have the two sheets open side by side.
