sum of the last 5 cells with a value I am trying to add the sum of the last 5 ga

Question

sum of the last 5 cells with a value

I am trying to add the sum of the last 5 games "plus minus" record for my players on my team. In other words looking at a range backwards (row) and add up the last 5 values. As some boys may have not been present at all the games, the cell could be blank so the formula has to count for a value and stop after seeing 5 values. Please note that there will be zero values which will need to be counted as part of the last five games. Hopefully this is clear.   

Thanks in advance~!!!

Explanation / Answer

Hi,

Try this ARRAY formula, see below on how to enter it. Note If there are less than 5 numbers in the list it will sum all of them and when the number is greater that 5 it will sum the last 5

=SUM(INDEX(A1:A100,LARGE(IF(NOT(ISBLANK(A1:A100)),ROW(A1:A100)-CELL("ROW",A1:A90)+1),5
)):INDEX(A1:A100,ROWS(A1:A100)))

Change 100 to a number larger than your range

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.