Formula help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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)*(A2:A11=1)*(A3:A12=1)*(A4:A13<>1))

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

XL2002, WinXP
 
thanks for all ur help guys!

Ron Coderre said:
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)*(A2:A11=1)*(A3:A12=1)*(A4:A13<>1))

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

XL2002, WinXP
Click to expand...
 
Back
Top