Count Formula

[mveda2004]

Hello Everyone,

I am trying to see if I can get an excel formula to do the following.

I am trying to create an absenteeism report where the report shows count of absenteeism and sum of hours. Some things to keep in mind are:

Absenteeism contrues of many codes: Ex: SICK, MIA, IPP, ULOA etc.
If Absenteeism occures concurrently then it is considered one occurence. If there is a break in between, then it is considered two occurences.

So if Cindy is SICK on 01/01/2013, 02/01/2013 and 03/01/2013 then it would be considered as one occurence because it happened concurrently.
Mike on the other hand had SICK on 02/01/2013 and 05/01/2013 it would be considered as 2 occurences
Rohit had sick fo 01/01/2013, 02/01/2013 and CODE late for 03/01/2013 then he would have 1 occurence of sick and 1 occurence of late

Is there any way to do this?

[Mayda89]

See the uploaded workbook.
Does it help?

[mveda2004]

Hi Mayda89,

Thankyou for the response, but I do not think that would suite my purpose. I have attached a file as to how the data will look.

In the sheet that I attached, I need my report to look something similar to what I have in my report summary tab. ofcouse what i showed there was all manual work and not feasible to do for all employees.

[Mayda89]

Hi mveda2004,

If you could tell me what is wrong with the solution I've provided, I could modify it accordingly.

Cheers

[mveda2004]

Hi

How do I modify this formula to look at the rep name and sum all the sick based on rep name and date. See attached worksheet