Help Please. How to keep a running total from multible events.

D

Douglas Edward

New Member
Joined
Jul 24, 2018
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
I want to keep a running total of Points, Events, and Cashes from 40 events.
The players will vary from each event.
And the list of players in the "Total Table" will grow.
Also the Player "Rank" will be resorted after every event.

I'm still a beginner, and learning as I go.

What am I missing in my formula? It won't add if a player is not in all Event Tables.

And what is a simpler way since I will be adding 40 events?

Thanks for your help!

ABCDEFGHIJKLMNO

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Totals[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Event 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]Event 2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Player [/TD]
[TD="align: center"]Rank[/TD]
[TD="align: center"]Points[/TD]
[TD="align: center"]Events[/TD]
[TD="align: center"]Cashes[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Player [/TD]
[TD="align: center"]Points[/TD]
[TD="align: center"]Events[/TD]
[TD="align: center"]Cashes[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Player [/TD]
[TD="align: center"]Points[/TD]
[TD="align: center"]Events[/TD]
[TD="align: center"]Cashes[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=VLOOKUP([@[Player ]],Event1[[Player ]:[Points]],2,FALSE)+VLOOKUP([@[Player ]],Event2[[Player ]:[Points]],2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=VLOOKUP([@[Player ]],Event1[[Player ]:[Events]],3,FALSE)+VLOOKUP([@[Player ]],Event2[[Player ]:[Events]],3,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=VLOOKUP([@[Player ]],Event1[[Player ]:[Cashes]],4,FALSE)+VLOOKUP([@[Player ]],Event2[[Player ]:[Cashes]],4,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Just confirming ..

- Totals and each 'Event' is a separate Excel Table, but
- all in the one sheet &
- all with headings in row 2 &
- spaced every 5 columns (after the Totals table which is an extra column wide)?
 
Upvote 0
Good question.
What is the best way to set it up?
Should I use tables?
Should I keep it all on one sheet, or use separate sheets for each event?
Should I keep the same number of columns in each table?

I'm open to any suggestions.
 
Upvote 0
Douglas Edward said:
What is the best way to set it up?
Should I use tables?
Should I keep it all on one sheet, or use separate sheets for each event?
Should I keep the same number of columns in each table?
Click to expand...
Tables or not doesn't really matter for this suggestion, but I have set my sample up with tables.
Keep all the event tables the same size(ie same number of columns) with columns in the same order in each table & all side-by-side across a single sheet as shown below.
Then use formulas like these in the Totals table.

Excel Workbook
ABCDEFGHIJKLMNOPQ
1TotalsEvent 1Event 2Event 3
2PlayerRankPointsEventsCashesPlayerPointsEventsCashesPlayerPointsEventsCashesPlayer
3D116022A01A10011
4C215021B501B01
5A310021C10011C501
6B45020D8011D8011
7E5020E01E01
8F6010F601H01
9G010G01I601
10H010
11I6010
12
Totals
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Is this the best way to calculate the number of events since the last one?
2 3
Replies
25
Views
668
Alex Blakenburg
A
VinceF
  • Question Question
VBA To Copy & Paste Value
Replies
0
Views
352
VinceF
VinceF
M
  • Question Question
Recursive LAMBDA for progressive SMALL calculations
Replies
1
Views
293
macfuller
M
D
  • Question Question
How many times does an "x" occur before a "y"?
Replies
3
Views
321
Peter_SSs
Peter_SSs
JenniferMurphy
  • Question Question
How to tally tennis court pairings?
Replies
9
Views
645
JenniferMurphy
JenniferMurphy

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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