Instances that occur in a sequence

[timmycl_7]

Hi,

I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.

Is this possible?

E.g. if a person is absent for 1 day the formula assigns a value of 1
if a person is off for 3 days in succession the formula assigns a value of 1


Regards

[NBVC]

Can you show the data setup and the current formula used?

[timmycl_7]

The sheet is used as a work schedule as well. 9 Column headings -

Date, Shift1, Shift2 etc

The employees initials are entered into the field according to when they are supposed to be working. The spreadsheet is updated daily, if an employee is sick, the employees intials + sick are enterd. Currently I have just used a countif formula to count the number of times every instance of (Employee Initials)+sick.

I would say changnig the structure of the table might not be an option as I have not designed the table.

[NBVC]

I would like to see a sample if possible...Zipped XL spreadsheet.

[timmycl_7]

Sorry,

I have attached a sample this time. Sheet 1 contains the information and sheet 2 contains my formula. In the example PY is off 4 consective days. I want this to be counted as 1. Also CM was sick 1 day, I also want this to be counted as 1. I'm trying to count the number of occasions rather than the number of days.

Thanks

[NBVC]

See attached....

I added 5 helper rows (1 for each shift)... you can hide these if you wish.

These helper rows calculate the counts of singles and groups of 3 or more "sick" for each shift. Note these are array formulas... if you edit them you have to confirm them with CTRL+SHIFT+ENTER not just ENTER so that the { } brackets appear around the formula.

Then you just sum these up in your summary.