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

<[email protected]> wrote in message
news:[email protected]...
> 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"),MATCH(2,1/(A1:A100>0)),1)
):INDEX(A1:A100,MATCH(2,1/(A1:A100<>""))),0)

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

Hope this helps!

In article <[email protected]>,
[email protected] 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".