Sum Last 4 numbers in a column with dynamic number of blanks between the numbers

[ramjet81]

I have hurt my brain trying to figure this one out. I need to be able to sum the last four numbers in Column A. The number of blank cells between the numbers are dynamic. I have tried to look at the following functions: INDEX, COUNT, COUNTIF, FREQUENCY, etc.

Help!!! Anyone got an idea on how to solve?

Thanks

	A	B
1	Data	Sum of Last 4 Numbers
2
3	2	15
4
5
6	6	16
7
8
9
10	3	12
11
12
13	4	10
14
15	3
16
17
18	2
19	1

<tbody>
</tbody>

 

[sheetspread]

Excel 2010
	A	B
1	Data	Sum of Last 4 Numbers
2
3	2	15
4
5
6	6	16
7
8
9
10	3	12
11
12
13	4	10
14
15	3
16
17
18	2
19	1
Sheet5 (4)

Cell Formulas
Range	Formula
B3	{=IF(AND(A3<>"",ISERROR(SMALL(IF(A3:A19<>"",ROW(A3:A19)),4))=FALSE),SUM(A3:INDEX($A$1:A19,SMALL(IF(A3:A19<>"",ROW(A3:A19)),4))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ramjet81]

Sheetspread,

This did the trick for me. I have to go back and study the application of the "small" function in this formula and the application of the Array calculation so I can learn from this. Thank you so much.

ramjet81

 

[salim hasan]

see the video Excel Magic Trick 856: Last 5 Values In Column: Formulas For Adding, Averaging and Extracting Data - YouTube

 

[ramjet81]

Thanks, Salim.

That is another interesting approach in the Video. I will check that out too.

ramjet81

 

[sheetspread]

I have to go back and study the application of the "small" function in this formula and the application of the Array

You understand most of it already. You're selecting the four smallest (nearest) nonblank row numbers below and including the current nonblank cell in column A. You would use frequency for filtering unique values or something similar.

 

[ramjet81]

I savvy. Was this solution one that you had already solved before, or did you conceive the solution that quick? I'm impressed. I was trying to figure out how to count down to the last four non-blank numbers. Your solution was ingenious. Thank you very much.

 

[sheetspread]

I've seen similar versions of this problem so it just took a few tries to work exactly. Ctrl-Shift-Enter formulas are often needed to overcome the limits of sum/countif(s) and even sumproduct.

 

[ramjet81]

Thank you so much for your assistance.