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

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 2004

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.

VoG II 20:05 14 Sep 2004

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.

VoG II 20:14 14 Sep 2004

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.

VoG II 20:26 14 Sep 2004

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 2004

=E3+'Sheet2'!A3

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

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman