# Running total help...

#### Chiefee

##### New Member
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")

### 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
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.

Replies
29
Views
2K
Replies
6
Views
272
Replies
10
Views
288
Replies
4
Views
99
Replies
16
Views
353

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.

### Which adblocker are you using?

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

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