+ Reply to Thread
Results 1 to 5 of 5

Count Consecutive & Non consecutive days per given logic

  1. #1
    Registered User
    Join Date
    02-17-2017
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    3

    Count Consecutive & Non consecutive days per given logic

    Hi experts,

    Need a formula to count the instances as per the data given in the attached file.

    Logic is as follows:
    1) Need to count the instances based on plant + Identifier.
    2) If days are not consecutive then count them as per actual (e.g Cell C5)
    3) If days are consecutive up to 4 days, count them as one instance (e.g C6:C7 OR C8:C10)
    4) If 5 or more days are consecutive, count them as per actual (e.g C14:C18 OR C23:C28)
    5) Populate the count next to Plant + Identifier in column O.

    P.S. For counting manually I have used pivot to aggregate my data at Plant & Identifier and populated dates next to it but if there could be a way to directly count from the "data" sheet without having to create a pivot it will be best. Be careful though that on a single day per Plant+Identifer there could be more than 1 order (column A in data sheet).

    Thanks a lot
    Attached Files Attached Files
    Last edited by asimraza89; 10-26-2019 at 09:59 AM.

  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 2302
    Posts
    38,619

    Re: Count Consecutive & Non consecutive days per given logic

    Your sample is not particularly helpful. All of the expected answers are 1!!!!

    Please amend and show all possible rules in operation!
    Glenn



  3. #3
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Count Consecutive & Non consecutive days per given logic

    Hi ,

    See if this works on a larger set of data.

    I have used 3 helper columns which should make it easy for you to follow the logic and check its correctness.

    The logic used and the formulae will work only if the data is sorted plant-wise , identifier-wise , and actual start day-wise.

    Narayan
    Attached Files Attached Files
    Last edited by NARAYANK991; 10-26-2019 at 12:31 PM.

  4. #4
    Registered User
    Join Date
    02-17-2017
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    3

    Re: Count Consecutive & Non consecutive days per given logic

    Quote Originally Posted by NARAYANK991 View Post
    Hi ,

    See if this works on a larger set of data.

    I have used 3 helper columns which should make it easy for you to follow the logic and check its correctness.

    The logic used and the formulae will work only if the data is sorted plant-wise , identifier-wise , and actual start day-wise.

    Narayan
    WOW... It worked like a breeze and exactly how I wanted. It was easily taking me few minutes every time I had to do the counting manually and that too I was restricted to validate it for a number of identifiers only due to effort it takes but with this formula I was able to validate 50k+ rows data in seconds Man, I can't thank you enough for it.

    P.S. Thanks for using 3 helper columns as that helped me to understand it well. Did you also work out formula in one single column only and if yes I'd love to use that?
    Last edited by asimraza89; 10-26-2019 at 05:06 PM.

  5. #5
    Registered User
    Join Date
    02-17-2017
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    3

    Re: Count Consecutive & Non consecutive days per given logic

    Hi Glenn,

    I'm not sure which part of the logic I didn't articulate clearly. My objective was to count the number of times the dates are in order as per the logic is given.

    Though NARAYANK991 has solved it for me, let me try to clarify it here for your interest.

    1-Jan
    3-Jan
    4-Jan
    5-Jan
    8-Jan
    10-Jan
    11-Jan
    12-Jan
    13-Jan
    14-Jan
    15-Jan

    (dates highlighted in bold to indicate the start of new scenario)

    Applying the logic on the above data set,

    - If days are not consecutive then count them as per actual (dates justifying this logic are 1st and 8th Jan, hence the count comes as 1+1=2).
    - If days are consecutive up to 4 days, count them as one instance (dates range justifying this logic are 3rd to 5th Jan, hence the count comes as 1).
    - If 5 or more days are consecutive, count them as per actual (dates range justifying this logic are 10th to 15th Jan, hence the count comes as 6).

    Total count for this date range comes out to be 2+1+6 = 9 which I wanted to be populated in O column under Count for every Plant + Identifier.

    I hope this clarifies it now.

    Thanks
    Last edited by asimraza89; 10-26-2019 at 04:36 PM.

+ 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. How to count number of consecutive days
    By dobracik in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-05-2016, 03:10 PM
  2. Need formula to count consecutive days
    By emp2013 in forum Excel General
    Replies: 0
    Last Post: 06-27-2016, 01:57 PM
  3. Replies: 17
    Last Post: 08-03-2015, 06:15 AM
  4. Count Consecutive Days (with rule associated)
    By DUNC78 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2015, 10:18 AM
  5. [SOLVED] Count number of consecutive days and instances
    By Cortlyn in forum Excel General
    Replies: 7
    Last Post: 02-06-2015, 06:02 PM
  6. [SOLVED] How to count 10 consecutive week days
    By rjassal82 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-16-2013, 05:40 AM
  7. count consecutive negative days
    By pault22 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2010, 08:10 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