Sum last N values

[betgem]

Hi, everyone !

I have a column with a formula that returns a number or a FALSE.

1 FALSE
2 FALSE
3 0
4 FALSE
5 FALSE
6 FALSE
7 1
8 FALSE
9 FALSE
10 FALSE
11 2
12 FALSE
13 6
14 FALSE
15 FALSE

Now, the pattern of numbers and FALSE's is irregular, over hundreds of rows.

What I need is a formula that returns the sum of the last 4 numbers (not last 4 cells, which can all be FALSE).
The formula will be copied in every cell in the adjacent column, so in the example above, assuming the data is in column A, cell B14 should return a value of 9 (last 4 numbers from the A column, starting from the same row as the formula/cell in column B, and going back as many cells as required to have the sum of the last 4 numbers). Cell B15 in the above example should return 9 again, and so on.

Thank you in advance, any ideas/suggestions will be appreciated.

 

[shg]

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

0​

[/td][td][/td][td]

14​

[/td][td]C1: {=SUM(A20:INDEX(A:A, LARGE(ISNUMBER(A1:A20) * ROW(A1:A20), 4)))}[/td][/tr]

[tr][td]

2​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

8​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td]

7​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

20​

[/td][td]

FALSE​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

The curly braces around the formula indicate that it MUST be confirmed with Ctrl+Shift+Enter instead of just Enter. That will make the braces appear; you can't enter them manually.

 

[betgem]

Shg, thank you for the reply. The formula worked very well, does exactly what I wanted (just had to alter the first part of the range reference to an absolute one, so that I can "anchor" it to the top cell in my row before pasting the formula down the rows/cells).
Much appreciated

 

[shg]

You're welcome.