SOLVED

How do I auto-populate an amount value based off a category drop down list into a cell?

Copper Contributor

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

5 Replies

Re: How do I auto-populate an amount value based off a category drop down list into a cell?

Use a pivot table or SUMIF().

Re: How do I auto-populate an amount value based off a category drop down list into a cell?

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.
best response confirmed by Hans Vogelaar (MVP)
Solution

Re: How do I auto-populate an amount value based off a category drop down list into a cell?

@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))))``

Re: How do I auto-populate an amount value based off a category drop down list into a cell?

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?