Summing Data by Week Ending and Category

Hello! I am trying to put together a budget worksheet that will take my weekly budgeted amounts by category and compare them to my actual spent amounts according to what is entered into a register on another sheet. I have gotten everything to work except for the field where it would look at the register and pull all transactions for that date within only a given category and insert them into my budget sheet. On the attached example, I would want to put a formula into field D2 of the "Budget" page that would return a total of $2916.73. The same formula in D3 would return a total of $209.26 and so on.

Please help! I've been driving myself crazy trying to figure this one out...Budget Sample for Forum.xlsx

Thank you thank you thank you!!! That's been driving me batty and it works perfectly! You are a lifesaver!

Hurray! Glad I could help out!

If you believe this answers your question, please mark the thread Solved under the Thread options at the top.

Ok, I have tried and tried to get the actual spreadsheet to work like the sample spreadsheet does. It pulls the deposit category just fine, but it won't calculate the withdrawal categories. I even re-created from scratch just to make sure I was working with clean data ranges. Please help! This is my actual spreadsheet this time instead of a sample.

Actually, that doesn't work at all. It doesn't separate them according to the week ending date. For instance, the $600 mortgage transaction was done in the week of 9/27 but appears in the mortgage actual category on the budget page on 9/6. The whole idea is to see how things are totaling by category each week compared to the weekly budget. If it's coming through correctly, the only totals for 9/6/13 should be:

Deposit $1,073.62
Groceries: $112.70
Our Boys: $160.00

I just happen to be at the office for a corporate party tonight and saw your email. And I'd rather discuss spreadsheets than party it up, because I'm kind of a nerd.

Here's your spreadsheet in working order with all the formulas filled out. The hard part of working with a table is that the whole first block of equations needs to be done manually. And by manually, I mean not just typing it in, but literally D2: =sumifs(selecting the deposit range,selecting date range,select a2,select category range, select category). And then doing that again for D3:D13 but with withdrawal range. Not just copying that formula, but literally selecting the data for each of those equations.

I know the formula in each one looks the same, but if you don't manually select the data for the SUMIFS when writing the equations for each line of D in the first block, then it doesn't quite work. However, once I finished manually selecting the data range for each SUMIF in D2:D13, I was then able to copy and paste that entire block of formulas into each of the other blocks.

If I had just copied D3 into D4, Excel would have adjusted the references without telling me, even though the equation looks exactly the same.

So without further adieu, here's it is:

sumifs table is hard.xlsx

Woohoo!!! I finally got it working! I had to play a bit with the ifsums formula (probably because of all of the named ranges, but it is what it is). Thanks for your help.