how to get sum by using automatic range changing

[FMHasan]

Sum.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
2		10	Need formula like for Day 10 to 20 by automatic range system in C Column
3		10 Days Sum		Days
4	Client name	Total	Total	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th	11th	12th	13th	14th	15th	16th	17th	18th	19th	20th	21st	22nd	23rd	24th	25th	26th	27th	28th	29th	30th	31st
5	Square Toiletries Ltd	-	-
6	Square Food & Beverage Ltd	-																20,703,882.00
7	Square Pharmaceuticals Ltd	2,396,000.00								2,396,000.00
8	Square Hospitals Ltd	-
9	Square Textiles Ltd	-													4,831.50
10	Square Agrovet Ltd	-
11	Aegis	-
12	Tateeghar	-												18,000.00
13	Square Fashions Ltd	-
14	Sun Communication Ltd	-												3,982,765.81
15	Pran	-
16	Unitrend Limited	-
17	Option Advertising	-
18	Bangas Ltd	-
19	Electromart Ltd	-												970,125.00			719,950.00
20	Pioneer Insurace Ltd	-
21	Social Marketing Company Ltd	-
22	Akij Group	-																			90,880.00
23	Isphani Group	447,097.00					402,089.00	45,008.00													9,823,093.00
24	Bata Shoe	-
25	Singer Bangladesh Ltd	-
26	Plan Bangladesh Ltd	-
27	Brac Aarong	-
28	Mi Cement	-
29	Sohoz.com	-
30	Anwar Group	-
31	Asian Paints BD Ltd	-
32	HKG Steel Mills Ltd	-
33	Holcim Cement BD Ltd	-														2,001,562.30
34	Heidelberg Cement BD Ltd	5,786,987.00					5,786,987.00										2,930,482.19
35	Aspire to Innovet	-																			481,534.00
36	British Council	-
37	World Food Programme	-
38	GPH Ispat Ltd	-
39	Concord Group	-
40	Butterfly Marketing Ltd	-
41	Sheba Plartform Ltd	-
42	Banglalink Digital Communication	-
43	Abul Khair Group	-
44	Beximco Communication Ltd	-													1,520,852.00
45	Doodle	31,887.25									31,887.25
46	Intelzaa	-
47	Arla Foods Bangladesh Ltd	-
48	Savoy	-
49	UCB Fintech Company (Upay)	-
50	Walton Digitech Indulstries Limited	1,032,210.00								1,032,210.00
51	Bread & Butter	-
52	Bengal Media Corporation	-
53	World Vision	-
54	The Stractural Engineers Ltd	-
55	International Television Channel Ltd	1,120,000.00									1,120,000.00
56	Channel i (Impress Teleflim)	-
57	Shomvob Health Ltd	-
58	Disrupt	-
59	New Track (SD Rubel)	315,000.00								315,000.00
60	Berger Paints Bangladesh Limited	5,812,449.83					5,737,449.83		75,000.00
61	Others	-
62		16,941,631.08		-	#	#	11,926,525.83	45,008.00	75,000.00	3,743,210.00	1,151,887.25	#	#	4,970,890.81	1,525,683.50	2,001,562.30	3,650,432.19	###########	-	-	10,395,507.00	-	-	-	-	-	-	-	-	-	-	-	-	-
63
64
65
66
67
68
February 2023 (3)

Cell Formulas
Range	Formula
C5	C5	=SUM(D5:AH5)
H23	H23	=4091+40917
U35	U35	=167819+313715
B5:B62	B5	=SUM(OFFSET(D5:AH5,0,0,1,$B$2))
D62:AH62	D62	=SUM(D5:D61)

 

[awoohaw]

What do you mean by automatic range changing?
What do you want to happen when you enter a new value some where ?

1. Identify that cell you change,
2. identify the cell(s) that have a calculation that that cell affects,
3. what are the calculations (or what is a clear description of the output you want to see?)

 

[FMHasan]

What do you mean by automatic range changing?
What do you want to happen when you enter a new value some where ?

1. Identify that cell you change,
2. identify the cell(s) that have a calculation that that cell affects,
3. what are the calculations (or what is a clear description of the output you want to see?)

My Calculation Range Is D6:AH, Here total 31 column. I want to calculate Sum value suppose F6:Z6 range. I will put 1st ranges (6) except F text in A2 Column and last Ranges value (6) except Z in B2 column. and by changing this cell value sum of those range will be change. Result should be in column C6. Is it possible ?

 

[FMHasan]

1. Identify that cell you change,----- A2 & B2
2. identify the cell(s) that have a calculation that that cell affects,----- D6:AH6
3. what are the calculations (or what is a clear description of the output you want to see?)------ SUM OF THOSE COLUMN

 

[awoohaw]

xl2bb won't allow me to copy your entire range back into the post. But this should give you an idea.
I'm not a fan of offset, but it works well here, but I also did it using INDEX.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Start Day	End Day
2	11th	21st	Need formula like for Day 10 to 20 by automatic range system in C Column
3		10 Days Sum		Days
4	Client name	Total	Total	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th	11th	12th	13th	14th	15th	16th	17th	18th	19th	20th	21st	22nd	23rd	24th	25th	26th	27th	28th	29th	30th	31st
5	Square Toiletries Ltd	65											1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
6	Square Food & Beverage Ltd	65											1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
7	Square Pharmaceuticals Ltd	0								2396000
8	Square Hospitals Ltd	0
9	Square Textiles Ltd	4831.5													4831.5
10	Square Agrovet Ltd	0
11	Aegis	0
12	Tateeghar	18000												18000
13	Square Fashions Ltd	0
14	Sun Communication Ltd	3982765.81												3982765.81
15	Pran	0
Sheet1

Cell Formulas
Range	Formula
B5	B5	=SUM(INDEX($D$5:$AH$5,1,MATCH($A$2,$D$4:$AH$4,0)): INDEX($D$5:$AH$5,1,MATCH($B$2,$D$4:$AH$4,0)-1))
B6:B15	B6	=SUM(OFFSET(D6,0,MATCH($A$2,$D$4:$AH$4,0)-1,1,MATCH($B$2,$D$4:$AH$4,0)-MATCH($A$2,$D$4:$AH$4,0)))