An excel query

I have a spreedsheet laid out thus: column A Code B: players name C: price D: team name E: month total F: year total. My question is, someone gave me a lookup formula so I had all players listed alphebetically starting row 69-148 because certain players appear in different teams more than once which is - =VLOOKUP(B3,Scores,2) so i only have to enter the data once and it will put the players score automatically next to there name in ten different teams.
So what they score in the month is in column E. What I want is there to be a running total for the year in column F. I know it seems a bit long winded but any help would be appreciated.

Also every month I will be putting the scores in the Month column (E) back to zero but I want to keep the running total for the year in column (F).

  Jeffers22 19:59 14 Sep 04

Why knock youself out? Have a column for each month and the running annual total is just an autosum.

  VoG II 20:02 14 Sep 04

Well if you hadn't just added that bit I was going to suggest entering

=SUMPRODUCT((E1:E5)*(B1:B5=B1)*(D1:D5=D1))

in Column F and copying down. You would need to change all the "1:5" references to the actual rows that your data occupies. You will gather from the above that I had assumed that the spreadsheet held the scores for each month.

If you reset every month then I cannot see a way of retaining a running total unless every time you "reset" you also store the previous total somewhere.

Cos i have 10 teams of 11 players and it will take up so much room especially when printing.

  VoG II 20:05 14 Sep 04

Actually

=SUMPRODUCT((E$1:E$5)*(B$1:B$5=B1)*(D$1:D$5=D1))

if you are going to enter in F1 and copy down.

Thanks Vog the first player would be starting in E3 down to E13 does this make a difference?

  VoG II 20:14 14 Sep 04

You need to substitute 3 where I have put 1.

And where I have put 5 you need to put the number of the very last row of your data table.

However, my formula assumes that for each player and team combination, there is data for each month of the year so far. If that is not the case then my formula will not give you the year's total.

Thanks for your help Vog, unless you could actually see the spreadsheet its very hard to explain.

  VoG II 20:26 14 Sep 04

If you are going to clear the sheet each month then a better solution is a variation on what Jeffers22 suggested.

Have a second sheet (Sheet2) and before you clear the totals, copy them all, goto A3 on Sheet2, Edit/Paste Special, tick Values and click on OK.

Then on your main sheet the formula in F3 would be

=E3+'Sheet2!'A3

which you can copy down the sheet.

At the beginning of the year/season you will need to reset all of the values on Sheet2 to zero.

  VoG II 20:28 14 Sep 04

=E3+'Sheet2'!A3

This thread is now locked and can not be replied to.

AMD Ryzen release date, specifications and features: Three CPUs from the Ryzen 7 range now…

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…