Sum Data Across Columns and Rows between Dates

[ChetanPuri]

Hello Excel Team,
Just need help with a Sum formula to calculate the data on to Budget tab from July 2024-2025 (Source Sheet). The issue I get is that I have dates from Row I8 to Column BH 8. The Starting Date is 07 July 2023 and ends on 30 June 2024 and similarly I have data from I 9 to BH 135. On to my Budget Sheet I need to add the Data by Month End for Example Total Sales Revenue in July, August, Sep etc. I have included both the sheets below the 1st Sheet (Budget) that's where I want the Revenue by Month and second sheet is the source sheet which have the data by week.

Any help is appreciated.

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Monthly Trial Balance - Budget
3	© www.excel-skills.com.au			-	-	-	-	-	-	-	-	-	-	-	-
4	Class	Acc No	Account Description	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	Feb-2024	Mar-2024	Apr-2024	May-2024	Jun-2024
5	I-01G	IS-0105	Revenue	- 216,600.00	- 433,200.00	- 649,800.00	- 866,400.00	- 1,083,000.00	- 1,299,600.00	- 1,516,200.00	- 1,732,800.00	- 1,949,400.00	- 2,166,000.00	- 2,382,600.00	- 2,599,200.00
6
7
8
Budget

Cell Formulas
Range	Formula
D4	D4	=DATE(YEAR('[CP Budget Workbook3.xlsx]Setup'!$C$10)-1,MONTH('[CP Budget Workbook3.xlsx]Setup'!$C$10)+2,0)
E4:O4	E4	=DATE(YEAR(D4),MONTH(D4)+2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4	Expression	=COUNTIF(BudClass,"No key!")>0	text	YES
D3:O3	Expression	=ROUND(D3,2)<>0	text	YES

Book5
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
8	07-Jul-23	14-Jul-23	21-Jul-23	28-Jul-23	04-Aug-23	11-Aug-23	18-Aug-23	25-Aug-23	01-Sep-23	08-Sep-23	15-Sep-23	22-Sep-23	29-Sep-23	06-Oct-23	13-Oct-23	20-Oct-23	27-Oct-23	03-Nov-23	10-Nov-23	17-Nov-23
9	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98	3,015.98
10
11	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00	2,634.00
12	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99	2,861.71
13													2,146.28	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99	5,007.99
14	3,931.99	2,808.56
15		1,123.43	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99	3,931.99
16	13,236.47	13,236.47	13,236.47	13,236.47	13,236.47	13,236.47	13,236.47	13,236.47	13,236.47	13,236.47	7,563.70
17											5,387.25	12,570.25	12,570.25	12,570.25	12,570.25	12,570.25	12,570.25	12,570.25	12,570.25	12,570.25
18	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59	3,544.91
19											2,658.68	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59	6,203.59
20	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85	5,738.85
21	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60	5,072.57
22												2,029.03	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60	7,101.60
23	7,963.52	7,963.52	7,963.52	7,963.52	7,963.52	7,963.52	7,963.52	7,963.52	7,963.52	7,963.52	7,963.52	7,963.52	7,963.52	7,963.52	3,412.94
24															4,531.75	7,930.56	7,930.56	7,930.56	7,930.56	7,930.56
25	6,181.18	6,181.18	6,181.18	6,181.18	6,181.18	6,181.18	6,181.18	6,181.18	6,181.18	6,181.18	6,181.18	6,181.18	4,415.13
Revenue July 2024-2025

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I8:BH8	Expression	=$I$8>#REF!	text	NO

 

[ChetanPuri]

Okay, try this:

I suggest posting these into a new workbook first to get the proof of concept established. If it works then, then you have what you need to do.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4	Class	Acc No	Account Description	2024-07-01	2024-08-01	2024-09-01	2024-10-01	2024-11-01	2024-12-01	2025-01-01	2025-02-01	2025-03-01	2025-04-01	2025-05-01	2025-06-01	Total
5	I-01G	20.23.120.401	SIL-Revenue-ASP	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
6	I-01G	20.23.100.401	SIL-Revenue-DRW	134,228.76	167,044.60	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	301,273.36
7	I-01G	20.23.110.401	SIL-Revenue-KTH	21,093.65	26,367.06	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	47,460.72
8	I-01G	20.23.140.401	SIL-Revenue-PLM	133,580.18	167,531.60	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	301,111.78
Budget

Cell Formulas
Range	Formula
E4:O4	E4	=EDATE(D4,1)
D5:O8	D5	=LET( Data,'Revenue July 2024-2025'!$D$9:$L$43, AccNos,'Revenue July 2024-2025'!$B$9:$B$43, Dates,'Revenue July 2024-2025'!$D$8:$L$8, AccNo,$B5, TargetFOM,EOMONTH(D$4,-1)+1, TargetEOM,EOMONTH(D$4,0), SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)*(AccNo=AccNos)))
P5:P8	P5	=SUM(D5:O5)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5
6
7
8		Account No.	Region	2024-07-05	2024-07-12	2024-07-19	2024-07-26	2024-08-02	2024-08-09	2024-08-16	2024-08-23	2024-08-30
9		20.23.140.401	Palmerston	3,386.90	3,386.90	3,386.90	3,386.90	3,386.90	3,386.90	3,386.90	3,386.90	3,386.90
10		20.23.140.401	Palmerston
11
12		20.23.100.401	Darwin	2,781.06	2,781.06	2,781.06	2,781.06	2,781.06	2,781.06	2,781.06	2,781.06	2,781.06
13		20.23.100.401	Darwin
14
15		20.23.110.401	Katherine	5,273.41	5,273.41	5,273.41	5,273.41	5,273.41	5,273.41	5,273.41	5,273.41	5,273.41
16		20.23.110.401	Katherine
17
18		20.23.100.401	Darwin	4,151.53	4,151.53
19		20.23.100.401	Darwin		593.08	4,151.53	4,151.53	4,151.53	4,151.53	4,151.53	4,151.53	4,151.53
20
21		20.23.100.401	Darwin	13,272.11	13,272.11	13,272.11	13,272.11	13,272.11	13,272.11	13,272.11	13,272.11	13,272.11
22		20.23.100.401	Darwin
23
24		20.23.140.401	Palmerston	6,532.38	6,532.38	6,532.38	6,532.38	6,532.38	6,532.38	6,532.38	6,532.38	6,532.38
25		20.23.140.401	Palmerston
26
27		20.23.140.401	Palmerston	6,059.28	6,059.28	6,059.28	6,059.28	6,059.28	6,059.28	6,059.28	6,059.28	6,059.28
28		20.23.140.401	Palmerston
29
30		20.23.140.401	Palmerston	7,477.98	7,477.98	7,477.98	7,477.98	7,477.98	7,477.98	7,477.98	7,477.98	7,477.98
31		20.23.140.401	Palmerston
32
33		20.23.100.401	Darwin	8,350.88	8,350.88	8,350.88	8,350.88	8,350.88	8,350.88	8,350.88	8,350.88	8,350.88
34		20.23.100.401	Darwin
35
36		20.23.100.401	Darwin	4,853.33	4,853.33	4,853.33	4,853.33	4,853.33	4,853.33	4,853.33	4,853.33	4,853.33
37		20.23.100.401	Darwin
38
39		20.23.140.401	Palmerston	3,894.67	3,894.67	3,894.67	3,894.67	3,894.67	556.38
40		20.23.140.401	Palmerston						3,894.67	3,894.67	3,894.67	3,894.67
41
42		20.23.140.401	Palmerston	6,043.83	6,043.83	6,043.83	6,043.83	6,043.83	6,043.83	6,043.83	6,043.83	6,043.83
43		20.23.140.401	Palmerston
Revenue July 2024-2025

Thank you for all your effort, I tried establishing the new worksheet and still get the error.

 

[ChetanPuri]

Thank you for all your help, it works perfectly. Many thanks, Regards, Chetan

 

[awoohaw]

Thank you for all your help, it works perfectly. Many thanks, Regards, Chetan

What did you do differently?

 

[ChetanPuri]

What did you do differently?In my Source Sheet, I have if error formula, with Blanks so I changed it "0". As the Blank cells have no value just formula tha was the reason it didn't work

 

[ChetanPuri]

What did you do differently? On my source sheet for the Data I had if error Formula to leave it Blank, I changed it "0" and it worked perfectly fine

CP Budget Workbook3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3	© www.excel-skills.com.au			-	-	-	-	-	-	-	-	-	-	-
4	Class	Acc No	Account Description	Jul-2024	Aug-2024	Sep-2024	Oct-2024	Nov-2024	Dec-2024	Jan-2025	Feb-2025	Mar-2025	Apr-2025	May-2025
40	I-01G	20.23.120.401	SIL-Revenue - ASP	504,744.67	634,565.89	504,744.65	508,152.17	632,928.07	504,744.65	632,079.39	506,160.18	506,792.63	506,491.86	630,930.82
41	I-01G	20.23.100.401	SIL-Revenue - DRW	801,157.78	1,003,629.66	805,208.75	803,551.95	1,003,306.19	801,331.26	1,000,705.88	804,492.51	802,137.46	801,334.05	1,003,078.24
42	I-01G	20.23.110.401	SIL-Revenue - KTH	407,854.56	511,079.99	409,433.88	407,854.56	512,587.20	407,854.56	509,818.20	411,647.37	408,841.21	407,854.56	511,294.85
43	I-01G	20.23.140.401	SIL-Revenue - PLM	461,135.87	581,473.83	462,867.59	460,144.90	575,181.12	462,479.69	576,237.31	459,964.87	458,924.99	458,924.99	573,656.23
Budget

Cell Formulas
Range	Formula
D4	D4	=DATE(YEAR(Setup!$C$10)-1,MONTH(Setup!$C$10)+2,0)
E4:N4	E4	=DATE(YEAR(D4),MONTH(D4)+2,0)
D40:N43	D40	=LET(Data,'SIL- Participant Budget Working'!$R$9:$BQ$147,AccNos,'SIL- Participant Budget Working'!$F$9:$F$147,Dates,'SIL- Participant Budget Working'!$R$8:$BQ$8,AccNo,Budget!$B40,TargetFOM,EOMONTH(Budget!D$4,-1)+1,TargetEOM,EOMONTH(Budget!D$4,0),SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)*(AccNo=AccNos)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4	Expression	=COUNTIF(BudClass,"No key!")>0	text	YES
D3:O3	Expression	=ROUND(D3,2)<>0	text	YES

 

[awoohaw]

Okay, so based on the information I had available, did the suggestion I made work?

 

[ChetanPuri]

Okay, so based on the information I had available, did the suggestion I made work?

Definitley , can't thank you enough

 

[awoohaw]

you're welcome. But, I don't understand how you got errors with an absolutely clean workbook. I had no other data to cause errors.

 

[ChetanPuri]

you're welcome. But, I don't understand how you got errors with an absolutely clean workbook. I had no other data to cause errors.

you're welcome. But, I don't understand how you got errors with an absolutely clean workbook. I had no other data to cause errors.

Because the source sheet I shared with you had no formulas, it was just copy and paste where as my original file had formulas such as if error which you will notice, I have changed to 0 instead of ""

CP Budget Workbook3.xlsx
	P	Q	R	S	T	U	V	W	X
8	CPI (Inclusive)	No. of Weeks	05-Jul-24	12-Jul-24	19-Jul-24	26-Jul-24	02-Aug-24	09-Aug-24	16-Aug-24
9	176,118.84	52.00	3,386.90	3,386.90	3,386.90	3,386.90	3,386.90	3,386.90	3,386.90
10	176,118.84	52.00	0	0	0	0	0	0	0
11	144,615.35	52.00	2,781.06	2,781.06	2,781.06	2,781.06	2,781.06	2,781.06	2,781.06
12	144,615.35	52.00	0	0	0	0	0	0	0
13	274,217.47	52.00	5,273.41	5,273.41	5,273.41	5,273.41	5,273.41	5,273.41	5,273.41
14	274,217.47	52.00	0	0	0	0	0	0	0
15	215,879.70	52.00	4,151.53	4,151.53	0	0	0	0	0
16	215,879.70	52.00	0	593.08	4,151.53	4,151.53	4,151.53	4,151.53	4,151.53
17	690,149.72	52.00	13,272.11	13,272.11	13,272.11	13,272.11	13,272.11	13,272.11	13,272.11
18	690,149.72	52.00	0	0	0	0	0	0	0
19	339,683.77	52.00	6,532.38	6,532.38	6,532.38	6,532.38	6,532.38	6,532.38	6,532.38
20	339,683.77	52.00	0	0	0	0	0	0	0
21	315,082.49	52.00	6,059.28	6,059.28	6,059.28	6,059.28	6,059.28	6,059.28	6,059.28
SIL- Participant Budget Working

Cell Formulas
Range	Formula
S8:X8	S8	=R8+7
R9:X21	R9	=IFERROR(($P9/$Q9)*((1/(1/(MEDIAN(MIN($L9,$C$2+7),R$8-1,R$8+6)-MEDIAN(MAX($K9,$C$1)-1,R$8-1,R$8+6))))/7),"0")
P9:P21	P9	=IF(K9<J9,O9*(1+$C$3),O9)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R8:BQ8	Expression	=$R$8>$C$2	text	NO

 

[awoohaw]

OK. I still don't understand. I asked you to confirm the formula works with only the data and formulas I had posted. You said it failed.
Regardless, you figured it out.

Best wishes.