+ Reply to Thread
Results 1 to 8 of 8

How to count series of repetitions in column?

  1. #1
    Registered User
    Join Date
    11-25-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    13

    How to count series of repetitions in column?

    Hello, I'm a beginner user and the problem is when I have a lot of data in the A column (2 possible values: "yes" or "no").
    How I can count how many times value "no" is appeared in series?
    The result can be stored in column C (see uploaded file).
    For example:
    yes
    yes
    no 1
    yes
    no
    no
    no 3
    yes
    yes
    no
    no 2
    yes
    etc.

    Do I need to use array formulas or not?
    What function can solve this kind of problems?
    Excel file with example is uploaded.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    Ro
    MS-Off Ver
    2013
    Posts
    678

    Re: How to count series of repetitions in column?

    Hi,
    Use Countif:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-25-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    13

    Re: How to count series of repetitions in column?

    Are you sure?
    The result of =COUNTIF(A:A,"no") is number how many times "no" is appeared in column A ...

    I need how many times "no" appears in one series
    For example:
    picture.PNG

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3,998

    Re: How to count series of repetitions in column?

    This gets you close:

    C1 =IF(A1="yes","",ROWS(A$1:A1)-INDEX(MATCH(2,1/(A$1:A1<>"no")),0))

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3,998

    Re: How to count series of repetitions in column?

    This should do it:

    C1 =IF(AND(A1="no",A2<>"no"),ROWS(A$1:A1)-INDEX(MATCH(2,1/(A$1:A1<>"no")),0),"")

  6. #6
    Registered User
    Join Date
    11-25-2017
    Location
    New York
    MS-Off Ver
    2016
    Posts
    13

    Re: How to count series of repetitions in column?

    Thank you, that second works completely.
    Last edited by Blue11440; 12-14-2017 at 11:29 AM.

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3,998

    Re: How to count series of repetitions in column?

    You're welcome. Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  8. #8
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: How to count series of repetitions in column?

    *Deleted*

    (I didn't read the question properly)
    Design everything to be as simple as possible, but no simpler.

+ 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