Using formulas, how can I count number of consecutive non zero triples?

[dushkin]

Hi,
Suppose I have a column with the following values:

A
1
2
3
0
4
3
0
4
5
6
0
0
6
0
1
1

I need to count the number of the triplets without 0's.
So, in this example the triples would be: 1,2,3 and 4,5,6
Meaning my formula should return 2 to me.

How can I achieve that?
Thanks.

 

[Anthony47]

A generic formula:

=SUM(--((INDEX(RangeA,ROW(Range1))*INDEX(RangeA,ROW(Range2))*INDEX(RangeA,ROW(Range3)))>0))

RangeA: starts from row1 of the table column and terminate 2 rows after the end of the table
Range1: the range of the table
Range2: Range1 but shifted by 1 row
Range3: Range1 but shifted by 2 rows

For example
-the data table is in N4:N19
-the formula will be

=SUM(--((INDEX(N1:N21,ROW(N4:N19))*INDEX(N1:N21,ROW(N5:N20))*INDEX(N1:N21,ROW(N6:N21)))>0))

To be confirmed using Contr-Shift-Enter, non Enter alone

Try...