How do I calculate a conditional running balance
Posted on 2014-12-25
Hello --Note: Please read carefully before responding, as it gets a little tricky:
I need to calculate a running balance of items charged to our expense account from a base budget amount for each category. For example, if I spend $500 on groceries once a week and my budget is $5,000, I'd like to subtract the $500 or whatever I spend from the running total balance of groceries, which started at $5,000. I'd also like to do the same for other categories of expenses also on the same spreadsheet: so I might have a budget of $1,000 for medical expenses, and a budget of $400 for clothing, etc.
It sounds simple, but the issue I'm having on my worksheet is that my categories are not always in consecutive rows; for instance, I may have some grocery expenses in consecutive rows, but then I may have medical expenses and then back to grocery expenses, you get the point, yes? So I would need some sort of conditional sumif formula, (I think). To further complicate matters however, groceries is not my only criteria, I have a different base budget amount for about 600 different categories, which I have in a drop-down selection list in a column. With all of these different categories and base budget amounts, would I have to create a table of the starting budgets matched with the 600 different categories and then do a Vlookup to my main sheet? That's fine, but how would I then combine that with a conditional sum formula to keep a running balance for these categories on the same sheet? Lastly, I'd like to be able to pull all of this into a pivot table which would sum the individual amounts spent for each category, subtracted from the running balances by category, to give me my remaining spend/overage. I've got everything figured out except how to keep the running balance piece for each category and each indidvidual amount spent within that category without it double counting the starting balance in the pivot table.