sum cells in an excel column that fall between cells that fit a specific criteria?

  • Hi,
    I have an excel column that consists of a 1s or 0s like so: 1 1 1 0 1 1 1 1 0 0 1 0 0 0 0 0 0 1 1 1 1 1. I would like to be able to count how many 1s there are after there are 2 consecutive 1s and before 6 consecutive 0s (so in this case I would get a result of 6).
    I have to do this for about 200 columns so a formula that automatically does this would be really useful. Please can you tell me if it possible to do this, and, if so, how?
    Thanks very much for your help.

  • Hello and Welcome to the Forum ...:smile:


    Do you always have 22 rows ?


    And are your rules set once forever ... ( count how many 1s there are after there are 2 consecutive 1s and before 6 consecutive 0s )


    or they might change ...?


    Thanks for your clarification ...


    P.S. should you have a tiny sample file to attach ... that would be great ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    Quickly created your Test file for your review ...:smile:


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi,
    Thank you for your help.
    To answer your questions. 1) The number of rows can vary, and 2) the rules are set forever.
    I had a look at the file you sent, thanks so much for that, but it seems that the formula you used has errors if I change the ones and zeros.

  • Hi,


    Thanks for the info ...


    Would you mind posting a file with errors ... when you are changing the Ones and Zeros ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for the file ...


    But according to your own rule : count how many 1s there are after there are 2 consecutive 1s


    The exact is count is 2 ...and not 9 ...!!!


    see attached ...

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I did as you asked, file attached.
    Sorry, I can see why you misunderstood my rules, hopefully in the attached file it is clearer now what I want, I want to know how many ones there are between the first 2 consecutive ones and the first six consecutive zeros, there may be other consecutive ones or zeros that are in the section being counted, but I only want to count the ones.
    Thanks so much for helping me with this.

  • Hello,


    Should the formula in Version 3 work as expected ...


    A sample file from your part would still be required to handle your specific structure with over 200 columns ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi, thanks for trying, though it still seems to have problems with starting after two consecutive ones if I move the positions of these, and it has problems also if I move the position of the consecutive zeros. I will attach a sample data file.
    Thank you very much.

  • Hello Laura,


    Thanks for your sample data ...


    " Extremely minor info " ...


    Within the very same column ... you can have SEVERAL instances of counts .... !!! :gunner:


    How on earth could you expect I would guess such a data structure ... !!!!:ufo:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Oh sorry, I did not expect you to guess that. I know the data has an odd structure (I'm not responsible for that, I'm just trying to analyse it...). I was not aware the several instances of counts would be a problem, as I thought where the counting started and stopped would depend on where you pasted the formula. In case it is not clear, I want a separate count for each ID. I am sorry that I am not clear sometimes, I have very little stats/excel/data analysis education so it is hard to describe what I want sometimes...
    Thank you.

  • [SIZE=14px]Den röda djungelfågeln är helt galen !!![/SIZE]


    :sheep::sheep::sheep:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello Laura,


    Attached is your Version 3 ( with your sample data ...) :wink:


    Hope you will have Fun playing with it ... :sing:

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Once you have tested the formulas included in Version 3 ...


    Do not hesitate to share your comments ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hope you have not decided ... to give up ... :?:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    I did not get the notification that you had replied until friday and then have been having internet issues... thanks so much for the spread sheet. I really appreciate you taking the time to make it. It does exactly what I want, I've no idea how though ;).

  • Hi Laura,


    Glad to hear the formulas are producing the results you expected ...:wink:


    You have noticed that, provided you stick to the sheet format, you can copy the formulas ...and therefore extend the sheet to your 200 + samples ...


    When it comes to the array formulas ... is it important for you to understand how they operate ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Yes I noticed that, very helpful, thanks. It could be important for me to understand how the array formulas work in case I want to do something like this in the future, but it is not an urgent thing so you do not need to explain it to me if you do not want to.
    All the best
    Laura