Formula to return non-zero values in a list

[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

 

[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)

 

[Naoki]

hmm...that would give me any non-zero from list, but unfortunately, it
would give me ALL non-zero values from the list, I only want the last
2.

Essentially I'm actually trying to find a couple values that are
technically in the MIDDLE of a list, the only conditional being that
they are the very last 2 prior to any zero value.

 

[Frank Kabel]

Hi
try the following formula:
=SUM(OFFSET($A$1,MATCH(0,A1:A100,0)-2,0,-3))

Note: at least two non-zero values must exist

 

[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