SUMIF function summing the last 6 rows

P

Part16

New Member
Joined
Sep 7, 2014
Messages
19
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]
 
Sort by date Sort by votes
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!!
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
Part16 said:
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!!
Click to expand...

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:

[TABLE="width: 288"]
<COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]5[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]C[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]B[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]C[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]B[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]C[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]5[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]5[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]B[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]C[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]B[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]C[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]5[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

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.
 
Upvote 0
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!
 
Upvote 0
Part16 said:
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!
Click to expand...

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

[TABLE="width: 240"]
<COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"]Last[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]B[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]Team[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]C[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]C[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]B[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: #daeef3"]C[/TD]
[TD="class: xl67, width: 64, bgcolor: #daeef3"]5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]B[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64, bgcolor: #daeef3"]C[/TD]
[TD="class: xl69, width: 64, bgcolor: #daeef3"]3[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]B[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64, bgcolor: #daeef3"]C[/TD]
[TD="class: xl69, width: 64, bgcolor: #daeef3"]5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

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:
Rich (BB code): 
=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)))
 
Upvote 0
PERFECT!!!!!!

Thank you very much!

One more stone to this sports data spreadsheet...

Your help is priceless...
 
Upvote 0
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?
 
Upvote 0
Part16 said:
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?
Click to expand...

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

Control+shift+enter, not just enter:
Rich (BB code): 
=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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Sumif with offset
Replies
2
Views
178
oldman33
O
S
  • Solved
SumIf problem
Replies
2
Views
316
sharshra
S
J
  • Solved
Using Month query inside sumif formula
Replies
6
Views
604
Jpull
J
C
  • Question Question
SUMIFS if a date is within the range
Replies
1
Views
629
Fluff
Fluff
J
  • Question Question
SUMIF Multiple Columns based on criteria next to each cell
Replies
3
Views
545
Fluff
Fluff

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top