Adding a given amount of rows

[Marvo]

If you had in column A a list of numbers, so A1=3, A3=1, A3=0, A4=1, A5=1, A6=3, A7=3, A8=0, A9=3, A10=1

and you put in cell C11 a given number, say for example 5,

what would be the formula required in Cell B10 to add the most recent 5 rows A6:A10 (Answer 10) or however many rows you ask for in C11?

Any help much appreciated

 

[shift-del]

Hi Marvo

Mappe9
	A	B	C	D
1	3
2	1
3	0
4	1
5	1
6	3
7	3
8	0
9	3
10	1
11			5	10
Tabelle1

Cell Formulas
Range	Formula
D11	D11	=SUM(INDEX(A1:A10,SEQUENCE(C11,,COUNT(A1:A10)-C11+1)))

 

[Marvo]

That's brilliant, thank you. Is there a way for that to work if a cell was blank and needs to be ignored? I have one criteria that would rule out certain rows.

 

[shift-del]

Please give an example with the expected result.

 

[Marvo]

So the criteria is All Matches or just League. I would like to look back at ALL matches, then just league matches. Maybe a filter?

 

[shift-del]

If I add the yellow rows that would be 12 for League and not 11, wouldn't it?

 

[Marvo]

Doh! Yes, you're right. Sorry I added them up in my head.

In the actual workbook (5,550 rows) I have a second column with just the league points showing and the cup games as blanks. That's why I was asking the follow up question. Obviously just one column would be better.

 

[shift-del]

Mappe9
	A	B	C	D	E	F
1	3	League
2	1	League
3	1	League
4	1	League
5	3	Cup
6	0	League
7	3	League
8	3	League
9	0	League
10	3	League
11	1	League
12				8	14	All
13					12	League
Tabelle1 (2)

Cell Formulas
Range	Formula
E12	E12	=SUM(INDEX(A1:A11,SEQUENCE(D12,,COUNT(A1:A11)-D12+1)))
E13	E13	=LET( league,FILTER(A1:A11,B1:B11="League"), matches,D12, SUM(INDEX(league,SEQUENCE(matches,,COUNT(league)-matches+1))))

 

[Marvo]

That is absolutely fantastic, thank you so much. Its even allowed me to get rid of a now unneeded column. Much obliged.

 

[shift-del]

You're welcome.