+ Reply to Thread
Results 1 to 2 of 2

How to find the maximum count of consecutive occurances in an excel range

  1. #1
    Registered User
    Join Date
    03-20-2015
    Location
    Bangalore
    MS-Off Ver
    Microsoft Office 2007
    Posts
    5

    How to find the maximum count of consecutive occurances in an excel range

    Can we have a formula for finding the maximum count in an excel range? For e.g If an employee has worked more than 6 days consecutively then it should give me 1 or else 0.

    Example: In the below e.g the employee has been present consecutively for 9 days. In this case the formula must give me “1”.

    EMP Name 1-Mar 2-Mar 3-Mar 4-Mar 5-Mar 6-Mar 7-Mar 8-Mar 9-Mar 10-Mar 11-Mar 12-Mar 13-Mar 14-Mar 15-Mar 16-Mar 17-Mar 18-Mar
    Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed
    Jack hadley WO P P P WO P P P P P P P P P WO P P P

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: How to find the maximum count of consecutive occurances in an excel range

    Hi. The result you want (I hope...) is in Column U of the attached sheet. It is an array formula and must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

+ 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. [SOLVED] How to count consecutive occurances of values greater than 0?
    By lhorner in forum Excel General
    Replies: 7
    Last Post: 05-01-2021, 11:03 AM
  2. Replies: 1
    Last Post: 03-20-2015, 03:56 AM
  3. VBA to find cell value in column and count occurances in same row within date range
    By Furutsun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2014, 11:55 AM
  4. [SOLVED] count the maximum number consecutive
    By Berna11 in forum Excel General
    Replies: 9
    Last Post: 07-13-2012, 11:27 AM
  5. Count the maximum consecutive TRUEs
    By tomgilb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2011, 12:23 AM

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