Calculate Difference Actual and Budget Based on Date

[kumara_faith]

HI,

I have the following table which records actual expenses:

Book1
	B	C	D
5	Date	Expense	Amount
6	Sunday, 3 September, 2023	Breakfast	50
7	Monday, 4 September, 2023	Dinner	30
8	Tuesday, 5 September, 2023	Travel	50
9	Wednesday, 6 September, 2023	Breakfast	60
10	Thursday, 7 September, 2023	Travel	30
11	Friday, 8 September, 2023	Travel	25
12	Saturday, 9 September, 2023	Book	80
13	Sunday, 10 September, 2023	Book	10
14	Monday, 11 September, 2023	Travel	15
15	Tuesday, 12 September, 2023	Book	25
16	Wednesday, 13 September, 2023	Donation	26
Sheet1

In the second table as below, I have the forecasted expenses:

Book1
	F	G	H
5	Date	Expense	Amount
6	Sunday, 3 September, 2023	Breakfast	50
7	Monday, 4 September, 2023	Dinner	100
8	Tuesday, 5 September, 2023	Travel	50
9	Wednesday, 6 September, 2023	Breakfast	60
10	Thursday, 7 September, 2023	Travel	200
11	Friday, 8 September, 2023	Travel	25
12	Saturday, 9 September, 2023	Book	80
13	Sunday, 10 September, 2023	Book	300
14	Monday, 11 September, 2023	Travel	15
15	Tuesday, 12 September, 2023	Book	200
16	Wednesday, 13 September, 2023	Donation	50
17	Thursday, 14 September, 2023	Travel	200
18	Friday, 15 September, 2023	Travel	25
19	Saturday, 16 September, 2023	Book	80
20	Sunday, 17 September, 2023	Book	300
21	Monday, 18 September, 2023	Travel	15
22	Tuesday, 19 September, 2023	Book	200
23	Wednesday, 20 September, 2023	Donation	50
24	Thursday, 21 September, 2023	Donation	50
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 Expense

920

 

[etaf]

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
	A	B	C	D
1	Date	Expense	Amount
2	2023-09-03	Breakfast	50	920
3	2023-09-04	Dinner	100
4	2023-09-05	Travel	50
5	2023-09-06	Breakfast	60
6	2023-09-07	Travel	200
7	2023-09-08	Travel	25
8	2023-09-09	Book	80
9	2023-09-10	Book	300
10	2023-09-11	Travel	15
11	2023-09-12	Book	200
12	2023-09-13	Donation	50
13	2023-09-14	Travel	200
14	2023-09-15	Travel	25
15	2023-09-16	Book	80
16	2023-09-17	Book	300
17	2023-09-18	Travel	15
18	2023-09-19	Book	200
19	2023-09-20	Donation	50
20	2023-09-21	Donation	50
forecast

Cell Formulas
Range	Formula
D2	D2	=SUMIFS(C2:C20,A2:A20,">"&MAX(actual!A2:A20))

Book7
	A	B	C
1	Date	Expense	Amount
2	45172	Breakfast	50
3	45173	Dinner	30
4	45174	Travel	50
5	45175	Breakfast	60
6	45176	Travel	30
7	45177	Travel	25
8	45178	Book	80
9	45179	Book	10
10	45180	Travel	15
11	45181	Book	25
12	45182	Donation	26
actual

here a dropbox link , only be on the share for a few days

maxdate forecast - ETAF.xlsx

Shared with Dropbox

www.dropbox.com

 

[kumara_faith]

Hi etaf,

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

 

[etaf]

you are welcome