Hi!

Assume your entries are in the range B1:W1

In B2 enter this formula:

=IF(C1<>B1,1,"")

In C2 enter this formula and copy across to W2:

=IF(D1<>C1,COLUMNS($B1:C1)-IF(COUNT($B2:B2),LOOKUP(2,1/ISNUMBER($B2:B2),COLUMN($B1:B1)-1),0),"")

To count the number of times 1 is entered in 3 consecutive cells:

=SUMPRODUCT(--(B1:W1=1),--(B2:W2=3))

Biff

"sparclight" <sparclight.1t7l32_1123106986.1745@excelforum-nospam.com> wrote

in message news:sparclight.1t7l32_1123106986.1745@excelforum-nospam.com...

>

> for example if you have values of

>

> 1 0 1 1 1 0 0 0 1 1 1 0 0 0 1 1 0 0 0 1 1 1

> how to count number of occurances where value of 1 is 3 consecutive

> times? Obviously answer is 3 in this instance but is there an excel

> formula to count/sum this?

>

> Thanks in advance

>

>

> --

> sparclight

> ------------------------------------------------------------------------

> sparclight's Profile:

> http://www.excelforum.com/member.php...o&userid=25885

> View this thread: http://www.excelforum.com/showthread...hreadid=392719

>
## Bookmarks