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![]()
Bookmarks