Counting number of consecutive zeros at the end of a list
 

I have a column of numbers like this:

5
7
0
5
0
0
Empty Cell
Empty Cell

And I want to count the number of consecutive zeros at the end of the
list ignoring any blank cells. In the above example the result would
be "2" - two consecutive zeros and the two blank cells are ignored.
Thanks for any help you can provide.

if the last non-blank cell is any value except zero then the result
shound be "0".

Try this

=MAX((A2:A20<"")*(ROW(A2:A20)))-MAX((A2:A20<0)*(A2:A20<"")*(ROW(A2:A20)))

it is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

wrote in message
oups.com...
I have a column of numbers like this:

5
7
0
5
0
0
Empty Cell
Empty Cell

And I want to count the number of consecutive zeros at the end of the
list ignoring any blank cells. In the above example the result would
be "2" - two consecutive zeros and the two blank cells are ignored.
Thanks for any help you can provide.

if the last non-blank cell is any value except zero then the result
shound be "0".

Try...

=COUNTIF(INDEX(A1:A100,IF(COUNTIF(A1:A100,"0"),MA TCH(2,1/(A1:A1000)),1)
):INDEX(A1:A100,MATCH(2,1/(A1:A100<""))),0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article .com,
wrote:

I have a column of numbers like this:

5
7
0
5
0
0
Empty Cell
Empty Cell

And I want to count the number of consecutive zeros at the end of the
list ignoring any blank cells. In the above example the result would
be "2" - two consecutive zeros and the two blank cells are ignored.
Thanks for any help you can provide.

if the last non-blank cell is any value except zero then the result
shound be "0".