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

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" wrote
in message ...

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

Here's another way...

Assuming that the second row contains your data, starting at B2,
try...

=SUMPRODUCT(--(COUNTIF(OFFSET(B2,0,ROW(INDIRECT("1:"&MATCH(9.999 99999999999E+307,B2:IV2)-2))-1,1,3),1)=3))

You can continue adding your data to the second row and the formula
will automatically update the count. Note that four consecutive values
of 1, if it exists, is counted twice.

Hope this helps!

sparclight Wrote:
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 :)


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=392719

Biff Wrote:
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"
wrote
in message
...

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



Sounds good but what if i have values in A1 to AA1 and all way down to
A54000 to AA5400 and need to put answer in AB1 all way down to
AB54000. it is hughe table of ones and zeros that i got stuck with:)


--
sparclight
------------------------------------------------------------------------
sparclight's Profile: http://www.excelforum.com/member.php...o&userid=25885
View this thread: http://www.excelforum.com/showthread...hreadid=392719

Try Domenic's approach.

Biff

"sparclight" wrote
in message ...

Biff Wrote:
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"
wrote
in message
...

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



Sounds good but what if i have values in A1 to AA1 and all way down to
A54000 to AA5400 and need to put answer in AB1 all way down to
AB54000. it is hughe table of ones and zeros that i got stuck with:)


--
sparclight
------------------------------------------------------------------------
sparclight's Profile:
http://www.excelforum.com/member.php...o&userid=25885
View this thread: http://www.excelforum.com/showthread...hreadid=392719