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" <
[email protected]> wrote
in message news:
[email protected]...
>
> 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