Sum number of rows

[bh24524]

Is there some kind of formula that can sum an amount or rows based on a number you type in? Like if for example, I want to sum only 50 rows one time, 300 another, 1000 yet another and so on is there some way I can do this without having to highlight different sections to see totals? Ideally if I could just type in 50 and it sums the first 50 rows in a specified column and then the same for any other number that is typed in. It would always start from the top.

 

[bh24524]

EDIT: nevermind. figured it out: =sum(offset($B$1,0,0,D1))

 

[Toadstool]

You beat me to it. INDIRECT is another way

BH24524.xlsx
	B	C	D	E	F	G
1	11		Rows to Sum		INDIRECT	OFFSET
2	22		7		308	308
3	33
4	44
5	55
6	66
7	77
8	88
9	99
10	111
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUM(INDIRECT("$B1:$B"&D2))
G2	G2	=SUM(OFFSET($B$1,,,D2))

 

[bh24524]

You beat me to it. INDIRECT is another way

BH24524.xlsx
	B	C	D	E	F	G
1	11		Rows to Sum		INDIRECT	OFFSET
2	22		7		308	308
3	33
4	44
5	55
6	66
7	77
8	88
9	99
10	111
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUM(INDIRECT("$B1:$B"&D2))
G2	G2	=SUM(OFFSET($B$1,,,D2))

Thank you for that though definitely. I wondered if INDIRECT could somehow be used for it. I am not familiar with INDIRECT so this will be something cool to play around with to. Thanks for sharing that!

 

[Peter_SSs]

You could also avoid the volatile function OFFSET (& INDIRECT)

=SUM(B1:INDEX(B:B,D1))

 

[bh24524]

You could also avoid the volatile function OFFSET (& INDIRECT)

=SUM(B1:INDEX(B:B,D1))

That's a cool one. Wouldn't have thought of that. Gonna play around with that one too. Thank you!

EDIT: the result was actually coming out different that the Offset or Indirect formulas. In one instance, I was summing only 12 rows which had a value of 10 in each row(that one was the easy one) so that should come out to 120 but using the mentioned formula, it came to 110. I used

=sum(A2:index(A:A,D1))

I do have one for the column B as well but A was easier since it was all values of 10. Did I do something wrong?

 

[bh24524]

That's a cool one. Wouldn't have thought of that. Gonna play around with that one too. Thank you!

EDIT: the result was actually coming out different that the Offset or Indirect formulas. In one instance, I was summing only 12 rows which had a value of 10 in each row(that one was the easy one) so that should come out to 120 but using the mentioned formula, it came to 110. I used

=sum(A2:index(A:A,D1))

I do have one for the column B as well but A was easier since it was all values of 10. Did I do something wrong?

Nevermind I see what I did wrong I should not have a header in the row

 

[Peter_SSs]

Your original example was summing from row 1 & now apparently you are not. Try something like this for a non-volatile version if data actually starts in row 2.
This is better anyway in that it now doesn't matter if rows are subsequently added or deleted.

20 12 12.xlsm
	A	B	C	D	E	F
1		Data		Rows		Sum
2		10		12		120
3		10
4		10
5		10
6		10
7		10
8		10
9		10
10		10
11		10
12		10
13		10
14		10
15		10
Sum

Cell Formulas
Range	Formula
F2	F2	=SUM(B2:INDEX(B:B,D2+ROW(B2)-1))

Or another option would be something like

=SUM(B2:INDEX(B2:B1000,D2))

 

[Peter_SSs]

... or if you have Excel 365 {I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)}

20 12 12.xlsm
	A	B	C	D	E	F
1		Data		Rows		Sum
2		10		12		120
3		10
4		10
5		10
6		10
7		10
8		10
9		10
10		10
11		10
12		10
13		10
14		10
15		10
Sum

Cell Formulas
Range	Formula
F2	F2	=SUM(INDEX(B2:B1000,SEQUENCE(D2)))

 

[bh24524]

... or if you have Excel 365 {I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)}

20 12 12.xlsm
	A	B	C	D	E	F
1		Data		Rows		Sum
2		10		12		120
3		10
4		10
5		10
6		10
7		10
8		10
9		10
10		10
11		10
12		10
13		10
14		10
15		10
Sum

Cell Formulas
Range	Formula
F2	F2	=SUM(INDEX(B2:B1000,SEQUENCE(D2)))

Got it and I edited the profile. Thank you it is working now.