+ Reply to Thread
Results 1 to 29 of 29

Find/count consecutive entries in a row in excel

  1. #1
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Find/count consecutive entries in a row in excel

    Hi everyone,

    I had a rather intriguing excel question for the forum.

    I have a row in excel that goes like this,

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

    I want to find the count of consecutive entries less than 5 and the count has to reset back to 0 if 5 is found and then start again from that 5 onwards

    for example

    1 2 3 4 --> gives 4
    1 2 3 4 5 1 2 3 --> gives 3

    1 2 3 4 5 1 2 3 2 1 2 5 1 3 2 1 4 4 3 2 5 --> gives 0

    My basic idea is to find the most number of days a person has been scoring lesser than 5 and ofc it resets if he/she able to get 5 or above on a certain day. Therefore each value on a row is per day basis. (column means dates)

    Help me out, Please. <3

    EDIT: I also want blanks to be considered as 0.

    real example: # 0.1 0.2 1 1.1 2 is the first row where '#' is blank, anything less than 1 should be counted and the first occurance of 1 or above value resets the count and counter starts again from that spot onwards, while ignoring all before it.

    # # 1.1 2 --> gives 2
    0.1 # 0.2 2 --> gives 3
    ## 0.1 1.1 0.1 --> gives 1
    if no other choice then i can replace all blanks with '0' too
    Last edited by reliableboy2; 10-03-2021 at 04:00 PM.

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Find/count consecutive entries in a row in excel

    You say:
    the count has to reset back to 0 if 5 is found
    So why doesn't 1 2 3 4 5 --> 0 just like your third example that ends in 5?

    If 1 2 3 4 5 --> 0 is incorrect then please explain further. Otherwise in the attached workbook cell A1 is the result and has the following formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Your sequence is entered in cell C1 up to cell W1

    Edit: this formula does not work if there are no 5's at all. See post #10 for a corrected formula.

    Hope this helps, let us know.
    Attached Files Attached Files
    Last edited by GeoffW283; 10-03-2021 at 07:06 PM.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,493

    Re: Find/count consecutive entries in a row in excel

    Here is the code for UDF
    Please Login or Register  to view this content.
    How to Use UDF code:
    In the developer tab click--> Visual Basic
    VB window opens
    Insert--> Module
    Paste the code.
    Close the VB window.
    Now UDF is available in Function List
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    Thank you, I have edited my post to correct the mistake.

  5. #5
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    Thank you, I know nothing of UDF, So I will try this and will let you know.

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Find/count consecutive entries in a row in excel

    Thanks for the added rep!

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,044

    Re: Find/count consecutive entries in a row in excel

    Hey,
    Why is Y2 a 1 instead of a 3?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  8. #8
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    My sheets is actually on onedrive. I tried ur formula and it doesnt work on web sheets. Is there any alternative?
    Last edited by reliableboy2; 10-03-2021 at 03:48 PM.

  9. #9
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Find/count consecutive entries in a row in excel

    Your edit earlier this afternoon to your post-1 requirements significantly changes the problem and puts all subsequent posts out of context which may confuse subsequent readers. It might have been better if your changed requirements were documented as a new post rather than editing post #1

    My sheets is actually on onedrive. I tried ur formula and it doesnt work on web sheets. Is there any alternative?
    I'm afraid I know nothing about Google sheets. The functions I use look like they are available in Google sheets. What exactly was the problem you saw? Your profile says MS Office version is 2019. Is that accurate?

    My post #2 formula (for the original "reset at 5" problem) had a problem - it didn't deal correctly with number sequences with NO 5's present. Here is the amended formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    real example: # 0.1 0.2 1 1.1 2 is the first row where '#' is blank, anything less than 1 should be counted and the first occurrence of 1 or above value resets the count and counter starts again from that spot onwards, while ignoring all before it.
    # # 1.1 2 --> gives 2
    0.1 # 0.2 2 --> gives 3
    ## 0.1 1.1 0.1 --> gives 1
    if no other choice then i can replace all blanks with '0' too
    Like with the original problem, you say:
    "the first occurrence of 1 or above value resets the count"
    so why does # # 1.1 2 give 2 and not 0 ?? Same question for 0.1 # 0.2 2 why does this give 3 and not 0?? Please clarify.

    Assuming that you can replace all blanks with '0' and if you agree with my expected results then the formula above with just one minor chaage in red should work.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Put the formula in C8 and your numbers in row-8 starting at col-C.

    Attached is a revised version of kvsrinivasamurthy's workbook.
    Attached Files Attached Files

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    Re: Find/count consecutive entries in a row in excel

    I have closed your follow-on thread, as it should not have been a separate thread.

    Please continue here.

    For the record, it sounds as if the OP has MS365 for Web Apps - the workbook is an Excel workbook (Microsoft credentials to log in) hosted on OneDrive.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  11. #11
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    so why does # # 1.1 2 give 2 and not 0 ?? Same question for 0.1 # 0.2 2 why does this give 3 and not 0?? Please clarify.
    update to our initial discussion, here anything above and equal to 1, the count has to reset, blank is considered 0 and 0.1 and 0.2 are to be counted.

  12. #12
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    My basic idea is to find the most number of days a person has been scoring lesser than 1 and of course it resets if he/she able to get 1 or above on a certain day. Therefore each value on a row is per day basis. (column means dates)

    I also want blanks to be considered as 0.

    Help me out, Please. <3

    real example: # 0.1 0.2 1 1.1 2 is the first row where '#' is blank, anything less than 1 should be counted and the first occurrence of 1 or above value resets the count and counter starts again from that spot onwards, while ignoring all before it.

    # # 1.1 2 --> gives 2
    0.1 # 0.2 2 --> gives 3
    ## 0.1 1.1 0.1 --> gives 1
    if no other choice then I can replace all blanks with '0' too coz the minimum score is 0.1 and maximum is 2.(possible values being 0.1, 0.2, 1, 1.1, 2)

    Here anything above and equal to 1, the count has to reset, blank is considered 0 and 0.1 and 0.2 are the ones to be counted.

    NOTE: I am currently using MS365 Web, so please let me know if it works there as well.

    my first row is D4:AH4

    GeoffW28's solution indicated that I must use '0' instead of blanks. His solution was

    Please Login or Register  to view this content.
    This formula worked perfectly when I tried it on MS excel on laptop but failed when it was used on MS365 web version.

    Thank you so much if you made it this far and I really appreciate the time and effort anyone puts into this. Peace!!

  13. #13
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    Quick note:

    To count: 0.1, 0.2, 0 and/or blanks
    Trigger to reset count: anything above or equal to 1 (1, 1.1, 2)

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    Re: Find/count consecutive entries in a row in excel

    There are instructions at the top of the page explaining how to attach your sample workbook.

    =IFERROR(COUNT($C8:$W8) - LOOKUP(2,1/($C8:$W8>=1), COLUMN($C8:$W8)-2), COUNT($C8:$W8))
    This formula worked perfectly when I tried it on MS excel on laptop but failed when it was used on MS365 web version.
    Fails in what way? Error message? Wrong result? There is NOTHING inherent to that formula that should fail in the web app version of Excel, as far as I can see.

    You really need to provide a workbook for testing and give more helpful detail about what's not working.
    Last edited by AliGW; 10-04-2021 at 04:35 AM.

  15. #15
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    Attached a rough work file for clarification.
    Attached Files Attached Files

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    Re: Find/count consecutive entries in a row in excel

    I saved to OneDrive, then went to the web app and opened it there:
    Attached Images Attached Images

  17. #17
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    The cell where i input the formula shows red border and says "the formula in this cell contains an error"

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    Re: Find/count consecutive entries in a row in excel

    OK - so, this begs a question: are you using Google Sheets after all? You REALLY need to get to grips with this.

    Provide a screenshot like mine that includes the menu and title bar of the app. Let's get this resolved once and for all, and then everybody can stop running round in circles.

  19. #19
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    Here is the attachment.
    Attached Images Attached Images

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    Re: Find/count consecutive entries in a row in excel

    Right - so that's a completely different workbook.

    Provide the workbook in the image for testing, please. It looks like you have failed to adapt the formula for the new workbook correctly.

    At least we know that you are using MS365 - that's good.

  21. #21
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    I cant attach links yet as it doesnt allow me to. Can i upload a txt file with the link in it?

  22. #22
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    updated formula for the targeted cells
    Attached Images Attached Images

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    Re: Find/count consecutive entries in a row in excel

    I can't troubleshoot a picture. Please attach the workbook itself. Stop prevaricating, please, and do as you have been asked. You are wasting time by ignoring simple requests.

  24. #24
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel


  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    Re: Find/count consecutive entries in a row in excel

    Try this:

    =IFERROR(COUNT($D4:$AH4) - LOOKUP(2,1/($D4:$AH4>=1), COLUMN($D4:$AH4)-3), COUNT($D4:$AH4))

    You have to update formulae to fit the dataset you are using.

    AliGW on MS365 Insider (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    AC
    AD
    AE
    AF
    AG
    AH
    AI
    1
    Total
    October
    2
    3
    No Name
    1
    2
    3
    4
    5
    6
    26
    27
    28
    29
    30
    31
    4
    1 000
    0.3
    0.1
    0.1
    0.1
    0
    0
    0
    0
    0
    0
    0
    0
    0
    31
    Sheet: Sheet1
    Last edited by AliGW; 10-04-2021 at 07:33 AM.

  26. #26
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    Thanks a lot, Can you please explain what that "-3" means? and how it was different from the earlier test sheets. Also why do I get negative results on some cells.

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    Re: Find/count consecutive entries in a row in excel

    Your range starts in column D (the FOURTH column). Therefore, in the section that returns the column of the array, you need to take away 3 so that it starts counting at 1 instead of 4 (i.e. D=1, E=2, etc.).

    Also why do I get negative results on some cells
    No idea. I haven't bothered trying to get my head around the exact nature of what it is you are trying to do, just the mechanics of the formula.

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Last edited by AliGW; 10-04-2021 at 07:49 AM.

  28. #28
    Registered User
    Join Date
    10-02-2021
    Location
    UAE
    MS-Off Ver
    2010, 2019, MS 365
    Posts
    17

    Re: Find/count consecutive entries in a row in excel

    The negative numbers can be fixed if blanks are replaced with 0 and only if those blanks appear behind the last trigger value in any row. Blanks after it are ignored.

    Capture.PNG

    And that solves my problem 99% (the remaining 1% being able to keep blanks as it is), but its something I can manage. Thanks once again to everyone who spent time on this and helped me with this. Thanks to the moderator who helped remove all the clutters and kept this post clean.

    Have a nice day.

  29. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,408

    Re: Find/count consecutive entries in a row in excel

    You're welcome.

+ 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. Count consecutive > zero, ignore zero count consecutive > zero next cell
    By karl.smith in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-16-2020, 07:58 AM
  2. Find count of unique entries using vba
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 33
    Last Post: 11-25-2019, 03:31 AM
  3. [SOLVED] Count Consecutive & Non consecutive days per given logic
    By asimraza89 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-26-2019, 04:31 PM
  4. [SOLVED] Find last occurance of value in column and count entries below
    By terratushi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2019, 02:50 PM
  5. Replies: 1
    Last Post: 03-20-2015, 03:56 AM
  6. How to find the maximum count of consecutive occurances in an excel range
    By manojsinha2006 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-20-2015, 03:47 AM
  7. [SOLVED] Counting the number of entries in a column (but only once for consecutive entries)
    By 11416498 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-30-2014, 09:36 AM

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