# An excel query

[email protected]® 19:46 14 Sep 2004
Locked

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.

[email protected]® 19:51 14 Sep 2004

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.

[email protected]® 20:03 14 Sep 2004

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

[email protected]® 20:10 14 Sep 2004

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

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.

[email protected]® 20:17 14 Sep 2004

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

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

Dell XPS 13 9370 (2018) review

The art of 'British' pulp fiction

Best password managers for Mac

TV & streaming : comment regarder le Tournoi des Six Nations 2018 ?