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

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

Please amend and show all possible rules in operation!

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

Originally Posted by NARAYANK991

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?

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