Forum Discussion
How do I auto-populate an amount value based off a category drop down list into a cell?
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.
For example:
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!
Thanks,
Kyle
KyleWalts Pivot table would do the task for you. Additionally you can use traditional SUMIFS() function like (as per my below screenshot.
=SUMIFS($B$2:$B$16,$A$2:$A$16,H2)With Microsoft-365 dynamic formulas, you can do these all in one go. Try-
=VSTACK(A1:B1,UNIQUE(HSTACK(A2:A16,SUMIFS(B2:B16,A2:A16,A2:A16))))Download the attached file for better understanding.
- Use a pivot table or SUMIF().
- Convert the Spreadsheet to table first. Next Pivot the table with category as row and amount in formulaes. It would better to add another column for the date or month and year which can add benefit to track expenses monthly and yearly.
KyleWalts Pivot table would do the task for you. Additionally you can use traditional SUMIFS() function like (as per my below screenshot.
=SUMIFS($B$2:$B$16,$A$2:$A$16,H2)With Microsoft-365 dynamic formulas, you can do these all in one go. Try-
=VSTACK(A1:B1,UNIQUE(HSTACK(A2:A16,SUMIFS(B2:B16,A2:A16,A2:A16))))Download the attached file for better understanding.
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!
- Glad to know. If it helps then please mark my answer as best response. You can use conditional formatting.
