Sums for a Table List

XLXLXL

Board Regular
Joined
Jan 10, 2011
Messages
52
Need some help with a formula for a table...

Table in the first image is created from data in the data sheet pictured in the second image. Both tabs are in the same spreadsheet. Each number in the list in column D of the first image is a sum of U in the separate data sheet tab for the particular player (player is C in this sheet, E in the data sheet).

MojoMastersPlayerTable.gif


The green and white block areas of the pic below (going down the sheet) are top 15 finishes in PGA golf events (tournaments). Each block contains 15 names and associated tournament data. This is the data entry area of the data sheet. Most of this is hidden, because it is not related to the problem. To the right is generated data. The farthest right columns, X and Y, are running sums of the number found in U (a tournament specific generated number). They are calculated using only data from the previous 15 golf tournaments (120) entries overall), in the case of X (Mojo Last 8), and only the previous 225 entries, in the case of Y (Mojo Last 15). Then the current event is added in for a total of 16 events. The total span is 135 rows for the X data and 240 rows for the Y data.

MojoMastersforMrExcel-1.gif


The latest entry of X and of Y are the highest for their respective ranges. I need to create a table like in image one, which contains each player (column E of the data sheet) represented in the range of the bottom 135 entries of the data sheet, each player listed one time (Formula 1 needed). The list will be placed in C2 of tab 2 (see below for tab names). Then I need a formula to return the sum of the U column data in the data sheet (image 2) over the 135 lines from the bottom of the column for each specific player represented in that range (the ones in the player list for the range created by formula 1). The number is already there in the data sheet in the X column, so what I think would be best would be to create the list of players in the range (this would be the first column of the list and placed in C2 down of tab 2). Then find the latest X for each player in the list in the range of the bottom 135 and return that number for each player represented to the table next to the player's name. I don't know how to do this, and I am having no luck. The database of data shown in pic 2 gets larger throughout the year with the addition of each tournament, so I cannot set specific ranges for the last 135 for the table. I just either have to find the last X in the datasheet over the last 135 rows for each player or sum up for the player from the bottom his U column entries over the last 135 rows.

Here is the formula for the data in the table in picture 1. The span here for calculating the number was the whole spreadsheet of data in the data sheet, 18-962. The players' names are all the players represented in the range. It sums U for each E and returns the total (to column D in image 1) next to the player's name (column C in image 1) in sheet 4:

=SUMIF(DataSheet!E$18:E$962,C2,DataSheet!U$18:U$962)

Basically, I need something similar but that either:

1. Sums the Us for the bottom 135 lines of the U column in the data sheet (pic 2) for each player

or

2. Returns the first column X value in the data sheet (pic 2) for each player from the bottom over the bottom 135 lines. The result will be compiled in tab 2.

Data Sheet is called: DataSheet
tab 4 is called: Top 15ers YTD
tab 2 is called: Top 15 Last 8
Spreadsheet is called: mojo_masters_2(1)

I am beside myself about this, so I would love any help whatsoever. Thankyou! :confused:
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

XLXLXL

Board Regular
Joined
Jan 10, 2011
Messages
52
Sorry...I just realized that the value for the table to go next to the player's name could also be the last X in the column for the player, since it is automatically the largest. As for the list of players, it would have to come from the last 135 rows, or the last X formula would show some of them as having earned an X value who have not done so over the last 135 rows....

So I need the last X for each player found in the range of the last 135 rows of the data sheet (the list is dynamic, growing by 15 lines a week, each added to the bottom of the data sheet). Players list goes to C2 of "Top 15 Last 8" and corresponding X value goes D2 of "Top 15 Last 8". "Top 15 Last 8" is tab 2...

I forgot to mention, the data sheet is called "DataSheet", and it is tab 5
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,903
Office Version
  1. 365
Platform
  1. Windows
Create 2 named ranges

Name: sumrange
Refers to: =OFFSET(INDEX(DataSheet!E:E,MATCH(1E+100,DataSheet!E:E),,,-135)

Name: criteriarange
Refers to: =OFFSET(INDEX(DataSheet!U:U,MATCH(1E+100,DataSheet!E:E),,,-135)

Then use formula

=SUMIF(criteriarange,criteria,sumrange)

Where criterairange and sumrange refer to the named ranges created, criteria refers to the existing criteria, i.e.C2 in your example formula =SUMIF(DataSheet!E$18:E$962,C2,DataSheet!U$18:U$962)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

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
Top