Results 1 to 5 of 5

Thread: Count consecutive occurrences when sum of two consecutive numbers below 3

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Count consecutive occurrences when sum of two consecutive numbers below 3

    Hello,

    I have a column of numbers :

    3
    0
    4
    2
    1
    0
    5
    0
    3
    2
    0
    2

    I am trying to get the number of times the sum of two consecutive rows is below 3. In this example 4 (i.e. 2+1,1+0,2+0 and 0+2).
    I tried a way around with this formula :

    =SUMPRODUCT(--(FREQUENCY(IF(C5:C16<2, ROW(C5:C16)), IF(C5:C16>=2, ROW(C5:C16)))>=2)) based on an old post but it is 1) not exactly what I am looking for and 2) not working anyway here for some reason I don't understand.

    THANK YOU VERY MUCH FOR YOUR HELP !

  2. #2
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count consecutive occurrences when sum of two consecutive numbers below 3

    My bad the title is confusing and should be "Count occurrences when sum of two consecutive rows below 3"
    Thanks

  3. #3
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,668
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Count consecutive occurrences when sum of two consecutive numbers below 3

    Hi. Your title doesnt match what you ask for in the post. One says below 3 then the other counts 3 as valid. Anyway this does less than 3:

    =SUMPRODUCT(--(A1:A12+A2:A13<3),--(A1:A12<>""),--(A2:A13<>""))

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Count consecutive occurrences when sum of two consecutive numbers below 3

    Quote Originally Posted by steve the fish View Post
    Hi. Your title doesnt match what you ask for in the post. One says below 3 then the other counts 3 as valid. Anyway this does less than 3:

    =SUMPRODUCT(--(A1:A12+A2:A13<3),--(A1:A12<>""),--(A2:A13<>""))
    Thank you very much it works !!

    You are right I made a mistake in my example, the result should be 3 not 4 (correction for people reading the thread in the future).

    If I may further ask

    1) Do you know why it is not possible to drag the formula across columns?
    2) Could you explain how the second/third arrays work?

    Many thanks

  5. #5
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,668
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Count consecutive occurrences when sum of two consecutive numbers below 3

    It is possible to drag it across columns. Excel will see a blank cell as zero so they are just testing to see if the cell really is zero or if its blank. Blank doesnt count but zero does.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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