SUMIF function summing the last 6 rows

Part16 · Sep 7, 2014

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))

A	5
B	4
C	3
A	3
B	4
C	5
A	5
B	4
C	3
A	3
B	4
C	5

FormR · Sep 7, 2014

Hi, welcome to the forum!

Here is one possibility:

Rich (BB code):

=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 · Sep 7, 2014

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 · Sep 7, 2014

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 · Sep 7, 2014

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 · Sep 7, 2014

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

Aladin Akyurek · Sep 7, 2014

Part16 said:
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 · Sep 7, 2014

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...)

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

Part16 · Sep 8, 2014

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 · Sep 8, 2014

Part16 said:
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?

SUMIF function summing the last 6 rows

Part16

New Member

Excel Facts

FormR

MrExcel MVP

Andrew Poulsom

MrExcel MVP

Aladin Akyurek

MrExcel MVP

Part16

New Member

Part16

New Member

Aladin Akyurek

MrExcel MVP

Part16

New Member

Part16

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

SUMIF function summing the last 6 rows

New Member

Excel Facts

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock