Formula to return non-zero values in a list

N

Naoki

I have a list of numbers in a column like so -

2
3
4
1
8
0
0
0
0

What formula would I create to always give me the a SUM of the las
couple values before the zero value. The list always ends with a valu
other than zero, so in this case, the '8' is the end of the list, and
would like to SUM the 3 "last" values, those being 8, 1 and 4. As
use this file, the zero values will automatically be updated with ne
information, so I need a formula which would constantly reflect thi
change
 
G

Guest

If you don't mind having an extra column then you can add
this in the next column and then sum it..

=IF(A4=0,A1,0)
 
D

dcronje

One idea:


=SUM(INDIRECT("A"&(MATCH(0,A:A,0)-3)&":"&"A"&(MATCH(0,A:A,0)-1)))

This will look for the first "0" in column A and then sum the thre
preceding numbers.

Hopefully this might help
 
Top