How do I count repeat Occurrences


K

kevhatch

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
 
Ad

Advertisements

F

FSt1

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
 
K

kevhatch

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
 
F

FSt1

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
 
K

kevhatch

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.
 
Ad

Advertisements

T

T. Valko

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

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top