How to sum last six numbers with different spaces

[skavorn]

I have an instance where I have a list of numbers in different rows with different amounts and I want to add up the last 6 numbers, they're all in the same column however they're not all in consecutive rows.

Also, the gaps between the rows is not consistent.

The example below explains what I mean (I've add numbers in between so you can see the varying gaps, but they're actually blank cells)

(B1)ADD ME = 2
(B2)**blank cell**
(B3)**blank cell**
(B4)**blank cell**
(B5)**blank cell**
(B6)ADD ME = 5
(B7)**blank cell**
(B8)**blank cell**
(B9)**blank cell**
(B10)**blank cell**
(B11)**blank cell**
(B12)**blank cell**
(B13)**blank cell**
(B14)**blank cell**
(B15)**blank cell**
(B16)**blank cell**
(B17)**blank cell**
(B18)ADD ME = 2
(B19)**blank cell**
(B20)**blank cell**
(B21)**blank cell**
(B22)**blank cell**
(B23)**blank cell**
(B24)**blank cell**
(B25)**blank cell**
(B26)**blank cell**
(B27)**blank cell**
(B28)**blank cell**
(B29)**blank cell**
(B30)**blank cell**
(B31)**blank cell**
(B32)ADD ME = 5
(B33)ADD ME = 3
(B34)**blank cell**
(B35)**blank cell**
(B36)**blank cell**
(B37)**blank cell**
(B38)**blank cell**
(B39)**blank cell**
(B40)**blank cell**
(B41)**blank cell**
(B42)**blank cell**
(B43)**blank cell**
(B44)**blank cell**
(B45)**blank cell**
(B46)ADD ME = 0
(B47)**blank cell**
(B48)**blank cell**
(B49)**blank cell**
(B50)**blank cell**
(B51)**blank cell**
(B52)**blank cell**
(B53)**blank cell**
(B54)**blank cell**
(B55)**blank cell**
(B56)**blank cell**
(B57)**blank cell**
(B58)**blank cell**
(B59)**blank cell**
(B60)ADD ME = 1
(B61)**blank cell**
(B62)**blank cell**
(B63)**blank cell**
(B64)**blank cell**
(B65)**blank cell**
(B66)**blank cell**
(B67)ADD ME = 5

What code can I add to say "add up the last 6 numbers containing values"?

[NBVC]

Try:

=SUM(INDEX(B1:B67,SMALL(IF(B1:B67<>"",ROW(B1:B67),""),COUNT(B1:B67)-6+1)):B67)

confirmed with CTRL+SHIFT+ENTER not just ENTER


where 6 is the n numbers from the bottom you want to sum

To make it more robust, so that if you are not starting at B1 or you insert rows above:

=SUM(INDEX(B1:B67,SMALL(IF(B1:B67<>"",ROW(B1:B67),""),COUNT(B1:B67)-6+1)-ROW(B1)+1):B67)

[skavorn]

The first one was spouting out an extremely long number.

However the second line worked perfect.

Thank you ever so much NBVC!