+ Reply to Thread
Results 1 to 6 of 6

Count number of instances in which two consecutive numbers in a column are positive

  1. #1
    Registered User
    Join Date
    06-05-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Count number of instances in which two consecutive numbers in a column are positive

    Greetings,

    I have a range (column) of numbers. I would like to count how many times a positive number is followed by another positive number.

    In my newbishness, I tried to do it with countif, but quickly realized that it doesn't allow for relative references to the cells it examines.

    If someone would be so kind to provide me a formula, I'll tweak it to fit my other needs.

    Thanks and cheers
    Last edited by DavidHD; 06-05-2011 at 12:51 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Count number instances in which two consecutive numbers in a column are positive

    Hi,
    One way

    Please Login or Register  to view this content.
    Note that the ranges are different.

    Can I also congratulate you on a well defined title. So many posters use totally useless headers.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-05-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Count number instances in which two consecutive numbers in a column are positive

    That works, thank you very much.

    So, if I get this correctly, countifs checks the first cells of both ranges and if both conditions are true, it counts them - and the trick is that the second range is one cell lower. So if I wanted to count, say, three consecutive positives, I only need to create a third range which is one cell lower again.

    What other methods are there? Not necessarily asking for the exact solution, just to know what to look into for future reference.

    Yeah - I try to make it as easy as possible to answer my question

    Cheers

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Count number instances in which two consecutive numbers in a column are positive

    Yes, you've got it exactly. It's what's known as an array formula - but be sparing with them. They can be extremely memory intensive and slow calculation down if ranges become very large.

    The other approach would have been to use a helper column and enter in B1
    Please Login or Register  to view this content.
    Copy this down column B and then use an =SUM() on column B or =COUNTIF(B1:B20,1).

    REgards

  5. #5
    Registered User
    Join Date
    05-11-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    3

    Unhappy Count each occurrence of every 5th consecutive "1"

    hi,
    I've tried many forums now, but cannot get this resolved

    I'm trying to count the occurrence of 1 within a range of columns (K10:S10). I have the formula, but it seemingly counts incorrectly.

    Example. To count every 5th occurrence, but if 1 appears ie 10 times in a row, I still get 1? It should be 2. However, if I have 1,1,1,1,1,2,1,1,1,1,1, then I get 2...seems it needs the "gap" before it starts counting again?

    Can you please help on this?
    My current array formula in use is: =SUM(IF(FREQUENCY(IF(K9:BH9=1,COLUMN(K9:BH9)),IF(K9:BH9<>1,COLUMN(K9:BH9)))>=2,1))

    Many thanks

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Count each occurrence of every 5th consecutive "1"

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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