+ Reply to Thread
Results 1 to 8 of 8

count consecutive cells matching string - start after 3

  1. #1
    Registered User
    Join Date
    09-02-2011
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    91

    Question count consecutive cells matching string - start after 3

    i want find formula to count consecutive cells matching string (e.g. 1) but the counting of the consecutive cells to start after the 3rd consecutive entry
    so if you have on a single row 5 consecutive times number 1 - the result should be 2 because the counting should start after 3rd entry
    if you have 8 consecutive entries of number 1 but somewhere between them there is blank cell that should be also considered as single consecutive entry and the result should be 5
    attached is example excel file
    Attached Files Attached Files
    Last edited by godlev; 12-07-2016 at 04:38 AM. Reason: Solved
    Truth fears no questions.

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: count consecutive cells matching string - start after 3

    Try this ...

    =COUNTIFS(B3:G3,1,C3:H3,1,D3:I3,1,E3:J3,1)

  3. #3
    Registered User
    Join Date
    09-02-2011
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    91

    Re: count consecutive cells matching string - start after 3

    thanks phuocam this gives the desired result!
    i was thinking for similar solution but...
    if the range is bigger like 100 cells in a row - the formula will be very long...
    maybe there is shorter way to express the formula itself?

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: count consecutive cells matching string - start after 3

    Quote Originally Posted by godlev View Post
    if the range is bigger like 100 cells in a row - the formula will be very long...
    Formula for 200 cell:

    =COUNTIFS(B3:GO3,1,C3:GP3,1,D3:GQ3,1,E3:GR3,1)

  5. #5
    Registered User
    Join Date
    09-02-2011
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    91

    Re: count consecutive cells matching string - start after 3

    Thank you phuocam - i missed to describe 1 big requirement in the beginning
    i modified the example file a little bit - would appreciate if you have idea how to count them this way ?

  6. #6
    Registered User
    Join Date
    09-02-2011
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    91

    Re: count consecutive cells matching string - start after 3

    would appreciate if anyone has idea how to count them and ignore the blank cells - im trying to think of some combinations but i always end up with long and complicated formula

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: count consecutive cells matching string - start after 3

    Try ...

    =SUMPRODUCT(--(MID(B3&C3&D3&E3&F3&G3&H3&I3&J3,ROW(INDIRECT("1:"&COLUMNS(B3:J3)-3)),4)="1111"))

  8. #8
    Registered User
    Join Date
    09-02-2011
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    91

    Re: count consecutive cells matching string - start after 3

    Thanks Phoucam
    i tried your last solution but cant seem to make it work

    i found another solution


    K3:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Consecutive cells count
    By moonbreakker in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-12-2015, 09:45 PM
  2. Count Uniques with String Matching
    By PedalPusher in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-19-2015, 07:40 PM
  3. Replies: 2
    Last Post: 07-25-2013, 01:01 PM
  4. Need to count max consecutive string of two values in a ROW
    By thehoorse in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-11-2013, 08:56 AM
  5. Replies: 1
    Last Post: 09-02-2011, 05:00 PM
  6. How to use SUMIF on consecutive cells matching criteria
    By panamakevin in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-29-2009, 01:38 PM
  7. Sum non-consecutive cells matching pre-set value
    By alpha417 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-12-2006, 12:30 PM

Tags for this Thread

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