Aug 08 2023 07:51 PM
Aug 08 2023 07:51 PM
Hello. Looking for some help here with a formula. I started a spreadsheet to help me track every expense, keep track of my budget, etc.
On one tab I have a running log of all my transactions for the month. Each transaction I have it categorized via a drop down list such as Bills, Essentials, Gas, Eating Out, etc. Then to the right of that column I have the amount of that transaction. On the 2nd tab I have a summary table for each item in the category drop down list(Bills, Essentials, Gas, Eating Out, etc.). What I would like to happen is when I add transactions and I label them a specific category, I would like on the other tab in my summary table to keep a running total amount of each transaction in that specific category.
Let's say I added 4 new transactions and I have them labeled under Bills and each bill was worth $25.
I would like the "Bills" row on my other tab to automatically add those 4 "Bills" totals and have $100 in that specific cell next to "Bills" under the Actual column in my picture. I have added 2 pictures. If anyone could help out, that would be fantastic! If you would like me to add any more photos or if I can attach the spreadsheet itself, I wouldn't mind. Just let me know!
Aug 08 2023 08:03 PM
Aug 08 2023 08:25 PMSolution
@KyleWalts Pivot table would do the task for you. Additionally you can use traditional SUMIFS() function like (as per my below screenshot.
With Microsoft-365 dynamic formulas, you can do these all in one go. Try-
Download the attached file for better understanding.
Aug 10 2023 08:23 PM
This was perfect and exactly what I needed! I used the traditional route and used the SUMIFS function.
Next question if you don't mind, in your example where it has the amounts, is there a way to automatically assign it a color when it hits over my budget amount and when I am under the budget amount.
For example, let's say my budget amount for "Essentials" is $500. In your table on your example, is there a way to make cell I3 automatically turn green when it is UNDER my budget and automatically turn orange when it's within $50 and then finally automatically turn red when it is OVER my budget?
Thanks for your help!
Aug 10 2023 10:21 PM