Calculate Difference Actual and Budget Based on Date

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
951
Office Version
  1. 365
HI,

I have the following table which records actual expenses:

Book1
BCD
5DateExpenseAmount
6Sunday, 3 September, 2023Breakfast50
7Monday, 4 September, 2023Dinner30
8Tuesday, 5 September, 2023Travel50
9Wednesday, 6 September, 2023Breakfast60
10Thursday, 7 September, 2023Travel30
11Friday, 8 September, 2023Travel25
12Saturday, 9 September, 2023Book80
13Sunday, 10 September, 2023Book10
14Monday, 11 September, 2023Travel15
15Tuesday, 12 September, 2023Book25
16Wednesday, 13 September, 2023Donation26
Sheet1


In the second table as below, I have the forecasted expenses:
Book1
FGH
5DateExpenseAmount
6Sunday, 3 September, 2023Breakfast50
7Monday, 4 September, 2023Dinner100
8Tuesday, 5 September, 2023Travel50
9Wednesday, 6 September, 2023Breakfast60
10Thursday, 7 September, 2023Travel200
11Friday, 8 September, 2023Travel25
12Saturday, 9 September, 2023Book80
13Sunday, 10 September, 2023Book300
14Monday, 11 September, 2023Travel15
15Tuesday, 12 September, 2023Book200
16Wednesday, 13 September, 2023Donation50
17Thursday, 14 September, 2023Travel200
18Friday, 15 September, 2023Travel25
19Saturday, 16 September, 2023Book80
20Sunday, 17 September, 2023Book300
21Monday, 18 September, 2023Travel15
22Tuesday, 19 September, 2023Book200
23Wednesday, 20 September, 2023Donation50
24Thursday, 21 September, 2023Donation50
Sheet1



In the following cell , I am trying to summarize the difference between the actual expenses as of Sept 13 and the balance of the expense from the forecasted expenses. The correct answer is 920 which is the balance of the expense between Stp 14 to Sept 21 since the we have actual expense until Sept 13. Is there a way to build a formula to calculate this ? Appreciate all the help. 🙏

Total Balance Forecasted Expense920
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
920 is just the forecast from the date after the last expense actual is entered - its not a balance of comparing what has been spent v forecast

=SUMIFS(C2:C20,A2:A20,">"&MAX(actual!A2:A20))

Book7
ABCD
1DateExpenseAmount
22023-09-03Breakfast50920
32023-09-04Dinner100
42023-09-05Travel50
52023-09-06Breakfast60
62023-09-07Travel200
72023-09-08Travel25
82023-09-09Book80
92023-09-10Book300
102023-09-11Travel15
112023-09-12Book200
122023-09-13Donation50
132023-09-14Travel200
142023-09-15Travel25
152023-09-16Book80
162023-09-17Book300
172023-09-18Travel15
182023-09-19Book200
192023-09-20Donation50
202023-09-21Donation50
forecast
Cell Formulas
RangeFormula
D2D2=SUMIFS(C2:C20,A2:A20,">"&MAX(actual!A2:A20))


Book7
ABC
1DateExpenseAmount
245172Breakfast50
345173Dinner30
445174Travel50
545175Breakfast60
645176Travel30
745177Travel25
845178Book80
945179Book10
1045180Travel15
1145181Book25
1245182Donation26
actual


here a dropbox link , only be on the share for a few days
 
Upvote 0
Solution
Hi etaf,

Thank you for the solution and it worked. Appreciate it and have a great day ahead.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top