SUM last 6 cells with data (ignoring blanks in between)

[cjohnwalsh]

Hi All,

I'm really stuck on this formula and hope someone can make my Friday a happy one

I want to count back the last 6 cells in column S which has data (ignoring any blank cells) and add them up.

My data is ranging betwen S62:S143 (with S144 being the cell that I want to show the total for the last 6 cells with data). I have column headings and various other bits of data from S1:S61 that I want to exclude completely but need to keep for historical purposes.

Here is an example of some of the data in column S:

56
BLANK CELL
BLANK CELL
BLANK CELL
45
34
BLANK CELL
BLANK CELL
37
BLANK CELL
BLANK CELL
42
BLANK CELL
46
49
BLANK CELL
BLANK CELL
36
35

This is my formula so far:

=SUM(OFFSET(S62,0,0,COUNTA(S62:S143)))

I have tried adding a negative and positive number on the end of the COUNTA formula but all it does is count all the cells within that given heigh range, not a specific number of cells with data.

I'm open to suggestions and don't specifically need to use SUM. As long as I get a total.

Any help would be really appreciated.

Thanks,

Chris

[daddylonglegs]

Hello Chris,

Assuming you have at least 6 numbers in that range this "array formula" can be used to sum the last 6

=SUM(INDEX(S62:S143,LARGE(IF(S62:S143<>"",ROW(S62:S143)-ROW(S62)+1),6)):S143)

confirmed with CTRL+SHIFT+ENTER

[XOR LX]

Hello Chris,

Assuming you have at least 6 numbers in that range this "array formula" can be used to sum the last 6

=SUM(INDEX(S62:S143,LARGE(IF(S62:S143<>"",ROW(S62:S143)-ROW(S62)+1),6)):S143)

confirmed with CTRL+SHIFT+ENTER

Very nice. I guess we could also make a non-CSE AGGREGATE version since the OP has Excel 2013:

=SUM(INDEX(S62:S143,AGGREGATE(14,6,(ROW(S62:S143)-ROW(S62)+1)/(S62:S143<>""),6)):S143)

Regards

[cjohnwalsh]

Thanks guys!! You really have put a smile on my face for a Friday afternoon!

Really appreciate your hard work and quick responses!