+ Reply to Thread
Results 1 to 5 of 5

Count consecutive repeted values

  1. #1
    Registered User
    Join Date
    08-03-2005
    Posts
    2

    Count consecutive repeted values

    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

  2. #2
    Biff
    Guest

    Re: Count consecutive repeted values

    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
    >




  3. #3
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    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.99999999999999E+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!

    Quote Originally Posted by sparclight
    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

  4. #4
    Registered User
    Join Date
    08-03-2005
    Posts
    2
    Quote Originally Posted by Biff
    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
    >

    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

  5. #5
    Biff
    Guest

    Re: Count consecutive repeted values

    Try Domenic's approach.

    Biff

    "sparclight" <sparclight.1t8tj0_1123164417.706@excelforum-nospam.com> wrote
    in message news:sparclight.1t8tj0_1123164417.706@excelforum-nospam.com...
    >
    > 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" <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
    >> >

    >
    >
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1