Hopefully A Simple 'sum' Query!

Hi all..!

Now I'm not sure which keywords to use to help look for similar problems on search engines etc, so thought it would be easier to just explain my problem and hope theres a solution!

This has been bugging me for a while now - with no joy.... yet :o)


Ok, basically what i've got is a list of Players down column A and across each player's row is a score (between 0 and 5) for each game played in a sports season, going from left to right.

(EG: Fred Jones 0 0 2 3 2 1 1 0 0... and so on)

at the end of the row (eg Column P) I have a cell stating the total score achieved from his last 4 games/matches. (in the example above, this cell would give the total of 2 (1+1+0+0)).

Is there a function/formula i could use to let Excel calculate this figure everytime i enter a new score after future matches?

In summary: An excel function to add the SUM of the latest 'x' amount of cells (reading left to right along a row), which updates as new scores are added!

Hope this makes sense to anyone..

Many thanks. Carl

Try this (written for data in row 7):

Please Login or Register  to view this content.

The key here is the OFFSET formula. This is the keyword you want to search on to understand it better. Basically, it says to use column A as the "base" and to offset from there 0 rows, count the number of entries in row 7 between column A and column 0 (inclusive), then back off 4 column. That is the starting point. Then, the size of the range is 1 row and 4 columns.

Thanks for your time guys.. and, of course, your advice!

After 20 minutes of trial and error, based on your replies, I've found the formula to add up the 'x' (6 in this case) amount of previous scores:

=SUM(OFFSET(D5,0,COUNT(D5:AO5)-6,1,6))

Where D5 is the score for match 1, upto the final match of the season (match 38, cell AO5). I've added fictional scores for future matches with success!

Just out of curiosity, what effect does the '$' symbol have in a formula?

Thanks again... Carl

Cheers chippy!

The links were useful, and answered my query.. excellent

Thanks for the reply.