Running total help...

Chiefee

New Member
Joined
Feb 6, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
Hi,

I have googled and also read a few threads on this forum but i am unable to find what i am trying to do.

I have a weekly sheet which displays the total score for predicting football scores. The totals shown are formulated form another sheet from their predictions matched against the results. I cant think of a way to get excel to add these totals together to formulate an overall total score for the season as the weeks pass as when i enter the new predictions/results the totals will change accordingly to the current week. Everything i try just adds the current weeks score.

With this i currently manually click the sort button to work out positions, is there a way for excel to automatically do this for me?

Also once the overall total sheet is formulated, is these a way to notify of position changes each week. eg, Mark - down 5, Paul - up 2, Martin, Same.

I have downloaded the Xl2BB and hopefully attached my weekly sheet so i hope this helps.

Any help is much appreciated.

Regards

Paul

Football Prediction V1.xlsm
BCDEFGHI
5Player Name4 Pointers3 Pointers2 Pointers1 PointersTotal Points
61stMark300012
72ndPaul20008
83rdMartin10208
94thNigel10117
105thCraig10015
116thPaul01025
127thMike01003
138thMark Q00033
149thAndy00022
1510thLewis00011
Weekly Points
Cell Formulas
RangeFormula
E6E6=COUNTIF('Player Scores'!AO6:AO14,"4")
F6F6=COUNTIF('Player Scores'!AO6:AO14,"3")
G6G6=COUNTIF('Player Scores'!AO6:AO14,"2")
H6H6=COUNTIF('Player Scores'!AO6:AO14,"1")
I6:I15I6=SUM(E6*4,F6*3,G6*2,H6*1)
E7E7=COUNTIF('Player Scores'!I6:I14,"4")
F7F7=COUNTIF('Player Scores'!I6:I14,"3")
G7G7=COUNTIF('Player Scores'!I6:I14,"2")
H7H7=COUNTIF('Player Scores'!I6:I14,"1")
E8E8=COUNTIF('Player Scores'!Q6:Q14,"4")
F8F8=COUNTIF('Player Scores'!Q6:Q14,"3")
G8G8=COUNTIF('Player Scores'!Q6:Q14,"2")
H8H8=COUNTIF('Player Scores'!Q6:Q14,"1")
E9E9=COUNTIF('Player Scores'!AC6:AC14,"4")
F9F9=COUNTIF('Player Scores'!AC6:AC14,"3")
G9G9=COUNTIF('Player Scores'!AC6:AC14,"2")
H9H9=COUNTIF('Player Scores'!AC6:AC14,"1")
E10E10=COUNTIF('Player Scores'!AK6:AK14,"4")
F10F10=COUNTIF('Player Scores'!AK6:AK14,"3")
G10G10=COUNTIF('Player Scores'!AK6:AK14,"2")
H10H10=COUNTIF('Player Scores'!AK6:AK14,"1")
E11E11=COUNTIF('Player Scores'!AG6:AG14,"4")
F11F11=COUNTIF('Player Scores'!AG6:AG14,"3")
G11G11=COUNTIF('Player Scores'!AG6:AG14,"2")
H11H11=COUNTIF('Player Scores'!AG6:AG14,"1")
E12E12=COUNTIF('Player Scores'!M6:M14,"4")
F12F12=COUNTIF('Player Scores'!M6:M14,"3")
G12G12=COUNTIF('Player Scores'!M6:M14,"2")
H12H12=COUNTIF('Player Scores'!M6:M14,"1")
E13E13=COUNTIF('Player Scores'!AS5:AS14,"4")
F13F13=COUNTIF('Player Scores'!AS5:AS14,"3")
G13G13=COUNTIF('Player Scores'!AS5:AS14,"2")
H13H13=COUNTIF('Player Scores'!AS5:AS14,"1")
E14E14=COUNTIF('Player Scores'!U6:U14,"4")
F14F14=COUNTIF('Player Scores'!U6:U14,"3")
G14G14=COUNTIF('Player Scores'!U6:U14,"2")
H14H14=COUNTIF('Player Scores'!U6:U14,"1")
E15E15=COUNTIF('Player Scores'!Y6:Y14,"4")
F15F15=COUNTIF('Player Scores'!Y6:Y14,"3")
G15G15=COUNTIF('Player Scores'!Y6:Y14,"2")
H15H15=COUNTIF('Player Scores'!Y6:Y14,"1")
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
Everything i try just adds the current weeks score.
Are you deleting or overwriting the entries for the previous weeks? If so then you would need to use vba to update the sheet once after you have finished entering the new predictions.

Formulas only work with the information that is in the sheet now, not anything that was there but has now been removed.

There is a workaround for this using circular references, but this will only work for a one time change, not for regular changes.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,738
Messages
5,626,606
Members
416,195
Latest member
tonmcg

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