Basic formula in google sheets

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.

Hi! Another approach is to stay in the Transactions sheet and use a Filter View to show just the income transactions for just the current month.

How To:

  1. Start in Transactions sheet and click the Create a Filter button to filter your data.
  2. In the Category column, click the Filter, go to Filter by values section, click Clear, then select just the income categories you desire.
  3. 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:
=EOMONTH(TODAY(),-1)

Example:

  1. Now go back to the Create a Filter button and click Save as filter view. Rename the Filter View something like: This Month’s Income.

Example:
Screen Shot 2021-12-21 at 1.19.42 PM

Then, when you want to see this month’s income in the Transactions sheet, you can just choose this saved Filter View.

To get a quick total, select all the Amounts and look in the bottom right corner for the Quicksum.

Example:
Screen Shot 2021-12-21 at 1.50.42 PM

Happy Filtering! Hope this helps!

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).

=VLOOKUP("Home Insurance",'Monthly Budget'!$B$14:$G$109, 6,FALSE)

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.

='Monthly Budget'!H3

1 Like