Hi,
I have a spreadsheet that covers the year daily from Jan01 - Dec31. It
includes employees names and their shift pattern.
What I would like Excel to do if possible is to count the number of
instances an employee is off .. i.e. if an employee works Mon-Fri as their
shift, has Monday and Tuesday off sick, is in work for Wednesday and Thursday
then has Friday off sick .. Saturday and Sunday weekend then Monday sick
again, I would mark each cell that corresponds to the sick days and it would
return a total of 3 instances. Sorry if that is a bit long winded, hope it
makes sense.
Thanks for your help
Kev

hi
i think the countif functions might work for you. you were a bit sketchy
about details so..
say you marked each day off with the word "sick" in column g.
=countif(G2:G366,"sick")
look up countif in xl help for more details.

Regards
FSt1

"kevhatch" wrote:

Hi,
I have a spreadsheet that covers the year daily from Jan01 - Dec31. It
includes employees names and their shift pattern.
What I would like Excel to do if possible is to count the number of
instances an employee is off .. i.e. if an employee works Mon-Fri as their
shift, has Monday and Tuesday off sick, is in work for Wednesday and Thursday
then has Friday off sick .. Saturday and Sunday weekend then Monday sick
again, I would mark each cell that corresponds to the sick days and it would
return a total of 3 instances. Sorry if that is a bit long winded, hope it
makes sense.
Thanks for your help
Kev

Sorry but no, thats not exactly what I was after.... I don't want to count
the individual days but the occurrences .. i.e. if an employee had one sick
day that would be one occurrence as would two sick days together, so on a
Mon-Fri work week if they had Mon sick then Thu and Fri sick that would be
two occurrences .. is that any clearer.. hope so .. thanks

"FSt1" wrote:

hi
i think the countif functions might work for you. you were a bit sketchy
about details so..
say you marked each day off with the word "sick" in column g.
=countif(G2:G366,"sick")
look up countif in xl help for more details.

Regards
FSt1

"kevhatch" wrote:

Hi,
I have a spreadsheet that covers the year daily from Jan01 - Dec31. It
includes employees names and their shift pattern.
What I would like Excel to do if possible is to count the number of
instances an employee is off .. i.e. if an employee works Mon-Fri as their
shift, has Monday and Tuesday off sick, is in work for Wednesday and Thursday
then has Friday off sick .. Saturday and Sunday weekend then Monday sick
again, I would mark each cell that corresponds to the sick days and it would
return a total of 3 instances. Sorry if that is a bit long winded, hope it
makes sense.
Thanks for your help
Kev

hi
that presents a small problem. excel doesn't know 2 sick days is 1 occurance.
so you will have to mark each as an occurance.
and i think i may have steered you wrong. you need to do this by employee
and the countif just counts total sicks. so say your emps are in column a..
=sumproduct((A2:A366="Bob")*(G2:G366="occurance"))

Regards
FSt1

"kevhatch" wrote:

Sorry but no, thats not exactly what I was after.... I don't want to count
the individual days but the occurrences .. i.e. if an employee had one sick
day that would be one occurrence as would two sick days together, so on a
Mon-Fri work week if they had Mon sick then Thu and Fri sick that would be
two occurrences .. is that any clearer.. hope so .. thanks

"FSt1" wrote:

hi
i think the countif functions might work for you. you were a bit sketchy
about details so..
say you marked each day off with the word "sick" in column g.
=countif(G2:G366,"sick")
look up countif in xl help for more details.

Regards
FSt1

"kevhatch" wrote:

Hi,
I have a spreadsheet that covers the year daily from Jan01 - Dec31. It
includes employees names and their shift pattern.
What I would like Excel to do if possible is to count the number of
instances an employee is off .. i.e. if an employee works Mon-Fri as their
shift, has Monday and Tuesday off sick, is in work for Wednesday and Thursday
then has Friday off sick .. Saturday and Sunday weekend then Monday sick
again, I would mark each cell that corresponds to the sick days and it would
return a total of 3 instances. Sorry if that is a bit long winded, hope it
makes sense.
Thanks for your help
Kev

AH! yes it does present a small problem... I do need the occurrences not
individual days ... looks like i'll be carrying on with the manual
totalling!! thanks for your help tho, much appreciated.

"FSt1" wrote:

hi
that presents a small problem. excel doesn't know 2 sick days is 1 occurance.
so you will have to mark each as an occurance.
and i think i may have steered you wrong. you need to do this by employee
and the countif just counts total sicks. so say your emps are in column a..
=sumproduct((A2:A366="Bob")*(G2:G366="occurance"))

Regards
FSt1

"kevhatch" wrote:

Sorry but no, thats not exactly what I was after.... I don't want to count
the individual days but the occurrences .. i.e. if an employee had one sick
day that would be one occurrence as would two sick days together, so on a
Mon-Fri work week if they had Mon sick then Thu and Fri sick that would be
two occurrences .. is that any clearer.. hope so .. thanks

"FSt1" wrote:

hi
i think the countif functions might work for you. you were a bit sketchy
about details so..
say you marked each day off with the word "sick" in column g.
=countif(G2:G366,"sick")
look up countif in xl help for more details.

Regards
FSt1

"kevhatch" wrote:

Hi,
I have a spreadsheet that covers the year daily from Jan01 - Dec31. It
includes employees names and their shift pattern.
What I would like Excel to do if possible is to count the number of
instances an employee is off .. i.e. if an employee works Mon-Fri as their
shift, has Monday and Tuesday off sick, is in work for Wednesday and Thursday
then has Friday off sick .. Saturday and Sunday weekend then Monday sick
again, I would mark each cell that corresponds to the sick days and it would
return a total of 3 instances. Sorry if that is a bit long winded, hope it
makes sense.
Thanks for your help
Kev

A1:A366 = dates 1/1/2008 - 12/31/2008
B1:B366 = status (ex: S = sick)

=SUMPRODUCT(--(C1:C366="s"),--(C2:C367<"s"))

--
Biff
Microsoft Excel MVP


"kevhatch" wrote in message
...
Hi,
I have a spreadsheet that covers the year daily from Jan01 - Dec31. It
includes employees names and their shift pattern.
What I would like Excel to do if possible is to count the number of
instances an employee is off .. i.e. if an employee works Mon-Fri as their
shift, has Monday and Tuesday off sick, is in work for Wednesday and
Thursday
then has Friday off sick .. Saturday and Sunday weekend then Monday sick
again, I would mark each cell that corresponds to the sick days and it
would
return a total of 3 instances. Sorry if that is a bit long winded, hope it
makes sense.
Thanks for your help
Kev