can anybody give me a formula that will count the number of times the number
"1" shows up in two consecutive rows in a whole column.

eg

1
1

1

1
1
1

1
1

so check the column above and count how many time the number "1" is shown in
two consecutive rows = answer from above would be 2

thanks in advance

Isn't the answer 4 in your example?

Pete

Anthony wrote:
can anybody give me a formula that will count the number of times the number
"1" shows up in two consecutive rows in a whole column.

eg

1
1

1

1
1
1

1
1

so check the column above and count how many time the number "1" is shown in
two consecutive rows = answer from above would be 2

thanks in advance

Hi,

err nope answer is two as shown below


1
1 here

1

1
1
1

1
1 ..and here

any help appreciated

"Pete_UK" wrote:

Isn't the answer 4 in your example?

Pete

Anthony wrote:
can anybody give me a formula that will count the number of times the number
"1" shows up in two consecutive rows in a whole column.

eg

1
1

1

1
1
1

1
1

so check the column above and count how many time the number "1" is shown in
two consecutive rows = answer from above would be 2

thanks in advance

Ah, so the three 1's in consecutive columns do not count?

Assuming your first number starts in A2, enter this formula in B3:

=IF(AND(A3=1,A2=1,A4=0,A1=0),1,0)

then copy the formula down the column for as many 1's as you have. Then
just sum column B - you could put this formula in B1:

=SUM(B3:B100)

(or whatever).

Hope this helps.

Pete

Anthony wrote:
Hi,

err nope answer is two as shown below


1
1 here

1

1
1
1

1
1 ..and here

any help appreciated

"Pete_UK" wrote:

Isn't the answer 4 in your example?

Pete

Anthony wrote:
can anybody give me a formula that will count the number of times the number
"1" shows up in two consecutive rows in a whole column.

eg

1
1

1

1
1
1

1
1

so check the column above and count how many time the number "1" is shown in
two consecutive rows = answer from above would be 2

thanks in advance

I meant consecutive "rows", but posted too quickly!.

Pete

Pete_UK wrote:
Ah, so the three 1's in consecutive columns do not count?

Assuming your first number starts in A2, enter this formula in B3:

=IF(AND(A3=1,A2=1,A4=0,A1=0),1,0)

then copy the formula down the column for as many 1's as you have. Then
just sum column B - you could put this formula in B1:

=SUM(B3:B100)

(or whatever).

Hope this helps.

Pete

Anthony wrote:
Hi,

err nope answer is two as shown below


1
1 here

1

1
1
1

1
1 ..and here

any help appreciated

"Pete_UK" wrote:

Isn't the answer 4 in your example?

Pete

Anthony wrote:
can anybody give me a formula that will count the number of times the number
"1" shows up in two consecutive rows in a whole column.

eg

1
1

1

1
1
1

1
1

so check the column above and count how many time the number "1" is shown in
two consecutive rows = answer from above would be 2

thanks in advance

Maybe something like this?:

With
digits, or blanks in A1:A10

This formula counts the occurrences of exactly 2 consecutive 1's, bounded by
non-1's:

B1:
=((A1=1)*(A2=1)*(A3<1))+SUMPRODUCT((A1:A10<1)*(A 2:A11=1)*(A3:A12=1)*(A4:A13<1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Anthony" wrote:

Hi,

err nope answer is two as shown below


1
1 here

1

1
1
1

1
1 ..and here

any help appreciated

"Pete_UK" wrote:

Isn't the answer 4 in your example?

Pete

Anthony wrote:
can anybody give me a formula that will count the number of times the number
"1" shows up in two consecutive rows in a whole column.

eg

1
1

1

1
1
1

1
1

so check the column above and count how many time the number "1" is shown in
two consecutive rows = answer from above would be 2

thanks in advance

thanks for all ur help guys!

"Ron Coderre" wrote:

Maybe something like this?:

With
digits, or blanks in A1:A10

This formula counts the occurrences of exactly 2 consecutive 1's, bounded by
non-1's:

B1:
=((A1=1)*(A2=1)*(A3<1))+SUMPRODUCT((A1:A10<1)*(A 2:A11=1)*(A3:A12=1)*(A4:A13<1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Anthony" wrote:

Hi,

err nope answer is two as shown below


1
1 here

1

1
1
1

1
1 ..and here

any help appreciated

"Pete_UK" wrote:

Isn't the answer 4 in your example?

Pete

Anthony wrote:
can anybody give me a formula that will count the number of times the number
"1" shows up in two consecutive rows in a whole column.

eg

1
1

1

1
1
1

1
1

so check the column above and count how many time the number "1" is shown in
two consecutive rows = answer from above would be 2

thanks in advance