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

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

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Part16]

Thank you Aladin for your answer.

I am reading the function properties from online office assistant trying to understand my mistake and how all these are applied because as you can see I don't know almost anything about excel functions.

The function that worked correctly at the first time was this of Mr. Poulsom.

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

Let me try to show you an example of how I "decoded" it when I applied it to my file.

I have two sheets on my file, one named "Results" and the other named "Teams". "Results" sheet is a list why many values (dates, time, league and so on) that ends up to the column CL:CL which contains the goals scored by a team.

So when I tried to apply the above formula I thought of it like below.


A1 is the reference cell which in my case should be the first cell (CL1) to the left of the goals column (CM4:CM50000). So it should be replaced with "Results!CL1" , is that right?

"C" in my case should be the cell "Teams!B2" which is the cell that contains the name of the team (Teams!B:B, is the list with the team names)

B:B should be the column "Results!CM4:CM50000" that contains the goal number of each game where the team is involved.

The function has to be applied to the cell "Teams!C2" next to the name of each team so as to have the total goals scored during the last six games.

Thats all I can say, I don't have a better way to describe it.

In any case, solved or not solved, I have to say a big THANKS to all of you for your time and efforts!!

 

[Part16]

Forgot to tell you that the "Results" sheet contains more than 20.000 entries in rows including every game with odds values and results of the previous season. So it is kind of complicated for me.

 

[Aladin Akyurek]

Thank you Aladin for your answer.

I am reading the function properties from online office assistant trying to understand my mistake and how all these are applied because as you can see I don't know almost anything about excel functions.

The function that worked correctly at the first time was this of Mr. Poulsom.

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

Let me try to show you an example of how I "decoded" it when I applied it to my file.

I have two sheets on my file, one named "Results" and the other named "Teams". "Results" sheet is a list why many values (dates, time, league and so on) that ends up to the column CL:CL which contains the goals scored by a team.

So when I tried to apply the above formula I thought of it like below.


A1 is the reference cell which in my case should be the first cell (CL1) to the left of the goals column (CM4:CM50000). So it should be replaced with "Results!CL1" , is that right?

"C" in my case should be the cell "Teams!B2" which is the cell that contains the name of the team (Teams!B:B, is the list with the team names)

B:B should be the column "Results!CM4:CM50000" that contains the goal number of each game where the team is involved.

The function has to be applied to the cell "Teams!C2" next to the name of each team so as to have the total goals scored during the last six games.

Thats all I can say, I don't have a better way to describe it.

In any case, solved or not solved, I have to say a big THANKS to all of you for your time and efforts!!

Here is the workbook:

https://dl.dropboxusercontent.com/u/65698317/Par16%20SUMIF%20function%20summing%20the%20last%206%20rows.xlsx

that implements the formula as I invoked:

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

<COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY>
</TBODY>

D1, either with the C condition in the formula...

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

Or with the C condition in F1...

F1: C

and the formula in F2...

=SUMIF(OFFSET(INDEX(A:A;MATCH(9,99999999999999E+307;B:B));0;0;-MIN(6;COUNT(B:B)));F1;B:B)

adapted to your non-American system.

 

[Part16]

Hi Aladin,

I was just writting to the thread. After many hours of reading and testing I realized why the formula doesn't work on the long list.

The calculation is based on the last six rows of the total list while i want to get the last six results of each team and sum the total goals scored only at these last six games of the team.

So it has first to isolate these cells containing these games and then make the calculation of goals...

I don't know if this is understood. I will upload it on a link and send it to you.

Thank again for coming back!

 

[Part16]

Here is the link to the simplified file

http://we.tl/gAXmmbBhQT

 

[Aladin Akyurek]

Hi Aladin,

I was just writting to the thread. After many hours of reading and testing I realized why the formula doesn't work on the long list.

The calculation is based on the last six rows of the total list while i want to get the last six results of each team and sum the total goals scored only at these last six games of the team.

So it has first to isolate these cells containing these games and then make the calculation of goals...

I don't know if this is understood. I will upload it on a link and send it to you.

Thank again for coming back!

I trust you can adapt the following to your workbook yourself...

A	5			Last
B	4		Team	3
C	3		C	13
A	3
B	4
C	5
A	5
B	4
C	3
A	3
B	4
C	5

<COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY>
</TBODY>

Goal: Sum the last 3 scores of the C team.

D3: C (a team)

E2: 3 (last 3 games)

E3, control+shift+enter, not just enter:

=SUM(IF(ROW($A$1:$A$12)>=LARGE(IF($A$1:$A$12=$D3,
  IF(ISNUMBER($B$1:$B$12),ROW($A$1:$A$12))),E$2),
  IF($A$1:$A$12=$D3,$B$1:$B$12)))

 

[Part16]

PERFECT!!!!!!

Thank you very much!

One more stone to this sports data spreadsheet...

Your help is priceless...

 

[Aladin Akyurek]

PERFECT!!!!!!

Thank you very much!

One more stone to this sports data spreadsheet...

Your help is priceless...

You are welcome. Thanks for the kind feedback.

 

[Part16]

Good morning Aladin,

The truth is that without your help it would be impossible for me to do it earlier than a year!

I have one more question when you find time.

This function returns the results only when there are indeed 6 entries to calculate. There are teams though that haven't reach this number during a season or they have been promoted from a lower division and haven't played yet 6 games at the earlier stages of the league. At these cases the function returns the value #NUM probably because the criteria of the function can not be met.

Is there any way to overpass this problem and calculate it even if there is only one game as past result?

Furthermore could this function work also with AVERAGE instead of SUM?

 

[Aladin Akyurek]

Good morning Aladin,

The truth is that without your help it would be impossible for me to do it earlier than a year!

I have one more question when you find time.

This function returns the results only when there are indeed 6 entries to calculate. There are teams though that haven't reach this number during a season or they have been promoted from a lower division and haven't played yet 6 games at the earlier stages of the league. At these cases the function returns the value #NUM probably because the criteria of the function can not be met.

Is there any way to overpass this problem and calculate it even if there is only one game as past result?

Furthermore could this function work also with AVERAGE instead of SUM?

Still using the sample and the set up of post #16...

Control+shift+enter, not just enter:

=SUM(IF(ROW($A$1:$A$12)>=LARGE(IF($A$1:$A$12=$D3,IF(ISNUMBER($B$1:$B$12),ROW($A$1:$A$12))),
  MIN(E$2,SUM(IF($A$1:$A$12=$D3,IF(ISNUMBER($B$1:$B$12),1))))),IF($A$1:$A$12=$D3,$B$1:$B$12)))

You can replace the surrounding SUM with AVERAGE in order to calculate an average.