CODEWITHSUNDEEP
excel-formulaexcel-2010
jerkfaceroberts
jerkfaceroberts

Reputation: 11

Date Dependant Calculation

I've got a (Excel) sheet with running totals of income and expenses. The data from each day is fed into a weekly running total. I also have monthly expenses that I would like to include into the weekly total expense count/profit count but don't want to put each category of monthly expenses into each day's or week's input fields.

I would like to keep the monthly expenses in it's own input field but add the data to the corresponding week the bills were paid.

I've been at this for weeks but can only find solutions that end with circular references. Bonus challanges: I'm pretty happy with the size of each input field and don't want to add any more fields nor do I want to do macros or any VBA. I really just want a formula to take care of it.

Is there a way to have Excel stop adding to a total after a certain day?

Link to the sheet. https://drive.google.com/file/d/0B5qCnQJhT_vkNHZlaEpnTGRtUjQ/view?usp=sharing

Upvotes: 1

Views: 187

Answers (1)

Rosetta
Rosetta

Reputation: 2725

it seems like you are entering your expense directly into your "dashboard" am I right? E.g. the rent 875 in cell H13. If this is the case, without a date, then you can't get Excel to intelligently place the expense to the correct week report in your dashboard.

try creating an input table and all your dashboard figures should come from the input table, utilizing functions like SUMIFS and SUMIF.

Example

enter image description here

Then in your dashboard

formula in H13 would be =SUMIFS(E:E,C:C,"Rent",D:D,"Check")

formula in G4, =SUMIFS(E:E,C:C,"Food",D:D,"Cash")

formula in D23, =SUMIFS(E:E,B:B,51,C:C,"Rent",D:D,"Cash")

Upvotes: 1

Related Questions