Formula to return non-zero values in a list

  • Thread starter Thread starter Naoki
  • Start date Start date
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
 
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)
 
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.
 
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
 
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
 
Back
Top