SUMIF function summing the last 6 rows

[Part16]

Good mornign to all,

I am trying to sum the last 6 rows in a continuously growing table when a specific name in a different column (letters column) is met.

In the below example I would like to sum lets say the last 2 entries when the letter in the first column is C.

I can get the last 6 rows results with the below function but I can not apply SUMIF or anything like it to filter the data when a criteria is met.

Any help is very much appreciated.

=SUM(OFFSET(A1;MATCH(1E+30;A:A)-1;0;-6;1))

[TABLE="width: 30"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, welcome to the forum!

Here is one possibility:

=SUMIF(INDEX(A:A,MATCH(9.99999999999999E+307,B:B)-5):INDEX(C:C,MATCH(9.99999999999999E+307,B:B)),E1,INDEX(B:B,MATCH(9.99999999999999E+307,B:B)-5):INDEX(B:B,MATCH(9.99999999999999E+307,B:B)))

Excel Workbook
	A	B	C	D	E	F
1	A	5			C	8
2	B	4
3	C	3
4	A	3
5	B	4
6	C	5
7	A	5
8	B	4
9	C	3
10	A	3
11	B	4
12	C	5
Sheet1

 

[Andrew Poulsom]

Does this work for you?

=SUMIF(OFFSET(A1;MATCH(1E+30;B:B)-1;;-6;1);"C";OFFSET(A1;MATCH(1E+30;B:B)-1;1;-6;1))

 

[Aladin Akyurek]

Also:

=SUMIF(OFFSET(INDEX(A:A,MATCH(9.99999999999999E+307,B:B)),0,0,-MIN(6,COUNT(B:B))),"C",B:B)

which reduces the volatility to just one OFFSET. Although admissible, I get a bit uneasy with the B:B bit set up against a definite/calculated range. The COUNT(B:B) bit can be replaced with COUNTA(A:A) which would seem more coherent.

 

[Part16]

Hi All,

First of all I would like to thank all of you for your precious time and help. It is really very important to me.

Unfortunately I can not make it work for some reason.

I am making a sports database and in the first sheet I want to fill the goals scored and conceded and in another sheet I would like to have the sum of these goals for the last six games so as to calculate the average and make some other calculations.

Do these solutions work for multiple sheets?

 

[Part16]

Is there any way that I could attach the file so as to have a look?

 

[Aladin Akyurek]

Hi All,

First of all I would like to thank all of you for your precious time and help. It is really very important to me.

Unfortunately I can not make it work for some reason.

I am making a sports database and in the first sheet I want to fill the goals scored and conceded and in another sheet I would like to have the sum of these goals for the last six games so as to calculate the average and make some other calculations.

Do these solutions work for multiple sheets?

Try to list the names of the sheets involved ("first sheet" and "another sheet" do not help to adjust a proposed formula).

 

[Part16]

Hi Aladin,

The first sheet is called "Results" and the second is called "Teams".

The column with the goals is CL4:CL50000 (CL1 to CL3 have some subtotal functions calculating the average odds) of the sheet "results" and the column of the team names is B:B from the sheet "teams".

Although I made it work to a simplified form in one sheet in another file I can't do it to this one.

Below you may see an example of the data in "Results". The last 4 columns have the goals.

I want to apply the formula to the cell next of each team in the sheet "teams" .

(The names are in Greek sorry...)

[TABLE="width: 1052"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]League[/TD]
[TD]1[/TD]
[TD]Home[/TD]
[TD]X[/TD]
[TD]Away[/TD]
[TD]2[/TD]
[TD]HT GH[/TD]
[TD]HT GA[/TD]
[TD]FT GH[/TD]
[TD]FT GA[/TD]
[/TR]
[TR]
[TD]17/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,30[/TD]
[TD]ΛΙΒΕΡΠΟΥΛ[/TD]
[TD]4,25[/TD]
[TD]ΣΤΟΟΥΚ[/TD]
[TD]7,75[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,30[/TD]
[TD]ΑΡΣΕΝΑΛ[/TD]
[TD]4,50[/TD]
[TD]ΑΣΤΟΝ ΒΙΛΑ[/TD]
[TD]7,00[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]17/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,10[/TD]
[TD]ΓΟΥΕΣΤ ΜΠΡΟΜ[/TD]
[TD]3,20[/TD]
[TD]ΣΑΟΥΘΑΜΠΤΟΝ[/TD]
[TD]2,90[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,85[/TD]
[TD]ΓΟΥΕΣΤ ΧΑΜ[/TD]
[TD]3,30[/TD]
[TD]ΚΑΡΝΤΙΦ[/TD]
[TD]3,45[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,80[/TD]
[TD]ΝΟΡΓΟΥΙΤΣ[/TD]
[TD]3,20[/TD]
[TD]ΕΒΕΡΤΟΝ[/TD]
[TD]2,15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]17/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,10[/TD]
[TD]ΣΑΝΤΕΡΛΑΝΤ[/TD]
[TD]3,20[/TD]
[TD]ΦΟΥΛΑΜ[/TD]
[TD]2,90[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]3,50[/TD]
[TD]ΣΟΥΟΝΣΙ[/TD]
[TD]3,25[/TD]
[TD]ΜΑΝΤΣΕΣΤΕΡ Γ.[/TD]
[TD]1,85[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]18/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]4,15[/TD]
[TD]ΚΡΙΣΤΑΛ ΠΑΛΑΣ[/TD]
[TD]3,50[/TD]
[TD]ΤΟΤΕΝΑΜ[/TD]
[TD]1,65[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]18/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,12[/TD]
[TD]ΤΣΕΛΣΙ[/TD]
[TD]6,25[/TD]
[TD]ΧΑΛ[/TD]
[TD]12,50[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]19/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,20[/TD]
[TD]ΜΑΝΤΣΕΣΤΕΡ ΣΙΤΙ[/TD]
[TD]5,25[/TD]
[TD]ΝΙΟΥΚΑΣΤΛ[/TD]
[TD]9,00[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]21/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,22[/TD]
[TD]ΤΣΕΛΣΙ[/TD]
[TD]5,15[/TD]
[TD]ΑΣΤΟΝ ΒΙΛΑ[/TD]
[TD]8,50[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]24/8/2013
[/TD]
[TD]ΑΓΠ[/TD]
[TD]3,60[/TD]
[TD]ΦΟΥΛΑΜ[/TD]
[TD]3,30[/TD]
[TD]ΑΡΣΕΝΑΛ[/TD]
[TD]1,80[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]24/8/2013
[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,50[/TD]
[TD]ΕΒΕΡΤΟΝ[/TD]
[TD]3,65[/TD]
[TD]ΓΟΥΕΣΤ ΜΠΡΟΜ[/TD]
[TD]5,25[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]24/8/2013
[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,10[/TD]
[TD]ΝΙΟΥΚΑΣΤΛ[/TD]
[TD]3,30[/TD]
[TD]ΓΟΥΕΣΤ ΧΑΜ[/TD]
[TD]2,85[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]24/8/2013
[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,65[/TD]
[TD]ΣΑΟΥΘΑΜΠΤΟΝ[/TD]
[TD]3,45[/TD]
[TD]ΣΑΝΤΕΡΛΑΝΤ[/TD]
[TD]4,25[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]24/8/2013
[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,75[/TD]
[TD]ΣΤΟΟΥΚ[/TD]
[TD]3,30[/TD]
[TD]ΚΡΙΣΤΑΛ ΠΑΛΑΣ[/TD]
[TD]3,90[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]24/8/2013
[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,25[/TD]
[TD]ΧΑΛ[/TD]
[TD]3,20[/TD]
[TD]ΝΟΡΓΟΥΙΤΣ[/TD]
[TD]2,65[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]24/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]3,60[/TD]
[TD]ΑΣΤΟΝ ΒΙΛΑ[/TD]
[TD]3,35[/TD]
[TD]ΛΙΒΕΡΠΟΥΛ[/TD]
[TD]1,80[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]25/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]6,35[/TD]
[TD]ΚΑΡΝΤΙΦ[/TD]
[TD]4,25[/TD]
[TD]ΜΑΝΤΣΕΣΤΕΡ ΣΙΤΙ[/TD]
[TD]1,35[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]25/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,50[/TD]
[TD]ΤΟΤΕΝΑΜ[/TD]
[TD]3,50[/TD]
[TD]ΣΟΥΟΝΣΙ[/TD]
[TD]5,50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,30[/TD]
[TD]ΜΑΝΤΣΕΣΤΕΡ Γ.[/TD]
[TD]3,15[/TD]
[TD]ΤΣΕΛΣΙ[/TD]
[TD]2,80[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,12[/TD]
[TD]ΜΑΝΤΣΕΣΤΕΡ ΣΙΤΙ[/TD]
[TD]6,50[/TD]
[TD]ΧΑΛ[/TD]
[TD]11,50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,85[/TD]
[TD]ΓΟΥΕΣΤ ΧΑΜ[/TD]
[TD]3,30[/TD]
[TD]ΣΤΟΟΥΚ[/TD]
[TD]3,45[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,70[/TD]
[TD]ΚΑΡΝΤΙΦ[/TD]
[TD]3,25[/TD]
[TD]ΕΒΕΡΤΟΝ[/TD]
[TD]2,20[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,95[/TD]
[TD]ΝΙΟΥΚΑΣΤΛ[/TD]
[TD]3,35[/TD]
[TD]ΦΟΥΛΑΜ[/TD]
[TD]3,10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,55[/TD]
[TD]ΝΟΡΓΟΥΙΤΣ[/TD]
[TD]3,20[/TD]
[TD]ΣΑΟΥΘΑΜΠΤΟΝ[/TD]
[TD]2,35[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31/8/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,30[/TD]
[TD]ΚΡΙΣΤΑΛ ΠΑΛΑΣ[/TD]
[TD]3,20[/TD]
[TD]ΣΑΝΤΕΡΛΑΝΤ[/TD]
[TD]2,60[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/9/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,35[/TD]
[TD]ΓΟΥΕΣΤ ΜΠΡΟΜ[/TD]
[TD]3,25[/TD]
[TD]ΣΟΥΟΝΣΙ[/TD]
[TD]2,50[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/9/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]2,40[/TD]
[TD]ΛΙΒΕΡΠΟΥΛ[/TD]
[TD]3,25[/TD]
[TD]ΜΑΝΤΣΕΣΤΕΡ Γ.[/TD]
[TD]2,65[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1/9/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,90[/TD]
[TD]ΑΡΣΕΝΑΛ[/TD]
[TD]3,40[/TD]
[TD]ΤΟΤΕΝΑΜ[/TD]
[TD]3,20[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14/9/2013[/TD]
[TD]ΑΓΠ[/TD]
[TD]1,15[/TD]
[TD]ΜΑΝΤΣΕΣΤΕΡ Γ.[/TD]
[TD]6,35[/TD]
[TD]ΚΡΙΣΤΑΛ ΠΑΛΑΣ[/TD]
[TD]9,50[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Part16]

Hi All and thank you again for your valuable time,

Has anyone managed to understand if the problem is me, not applying the formulas correctly to the file or it is not possible to apply them on multiple sheets?

Below you may find the sheets names and ranges that I would like to use.

"The column with the goals is CL4:CL50000 (CL1 to CL3 have some subtotal functions calculating the average odds) of the sheet "results" and the column of the team names is B:B from the sheet "teams"."

Any form of help is appreciated.

Thank you in advance.

 

[Aladin Akyurek]

Hi All and thank you again for your valuable time,

Has anyone managed to understand if the problem is me, not applying the formulas correctly to the file or it is not possible to apply them on multiple sheets?

Below you may find the sheets names and ranges that I would like to use.

"The column with the goals is CL4:CL50000 (CL1 to CL3 have some subtotal functions calculating the average odds) of the sheet "results" and the column of the team names is B:B from the sheet "teams"."

Any form of help is appreciated.

Thank you in advance.

Hard to follow what you mean by multiple sheets...

Try to map the formula set up for your initial sample to your real data. Say at least to which ranges it must be applied: What is the range for which the condition like "C" must be run and which range must be summed?