# Counting Instances of Sick

1. ## Counting Instances of Sick

I have an excel spreadsheet which tracks staff whereabouts, laid out as following

Column A is a list of staff
Column B is a date
Column C is AM
Column D is PM
Column E is Notes

On the same sheet, I want to show total number of days per staff member and total number of instances.

For example staff member a may be sick on the following days

01/05/2011
02/05/2011
03/05/2011

11/11/2011

20/11/2011
21/11/2011

15/02/2012

That is 7 days sick but only 4 instances. how can i show this?

I did try: =MAX(FREQUENCY(IF(C1:D1000="Sick",ROW(B1:B1000)),IF(C1:D1000<>"Sick",ROW(B1:B1000)))) which brings back the total number of sicks in column c and column d but i was this per person

Paul

2. ## Re: Counting Instances of Sick

If somebody is sick on Friday and Monday is that 2 instances or do you work at weekends? Can somebody be sick in the morning the not in the afternoon and then sick again the next day - would that be separate instances?

3. ## Re: Counting Instances of Sick

Any continuous sick is one instance so off friday and monday is one instance (don't work weekends)

We have occassions where people off sick mon and tues, came back weds but went home weds pm, off thurs and fri again thats one instance.

So if they are sick on consecutive days either am/pm then that is a single instance regardless of how long that period is.

Does that help?

Paul

4. ## Re: Counting Instances of Sick

If you have a specific employee name in H2 then try this array formula in I2 for the number of discrete instances of sickness

=SUM(IF(A\$2:A\$1000=H2,IF((C\$2:C\$1000="sick")+(D\$2:D\$1000="sick"),IF(ISNA(MATCH(B\$2:B\$1000, IF(A\$2:A\$1000=H2,IF((C\$2:C\$1000="sick")+(D\$2:D\$1000="sick"),WORKDAY(B\$2:B\$1000+0,1))),0)),1))))

should work in Excel 2007 or later versions

5. ## Re: Counting Instances of Sick

just tried that but unfortunately it gives me an error

"The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

6. ## Re: Counting Instances of Sick

Your profile says you are using Excel 2010 - it should work OK in that version.....unless you are using "compatability mode"....

This version has a small change to reduce the nesting level - that should now work for you

=SUM(IF((A\$2:A\$1000=H2)*((C\$2:C\$1000="sick")+(D\$2:D\$1000="sick")>0),IF(ISNA(MATCH(B\$2:B\$1000, IF(A\$2:A\$1000=H2,IF((C\$2:C\$1000="sick")+(D\$2:D\$1000="sick"),WORKDAY(B\$2:B\$1000+0,1))),0)),1)))

7. ## Re: Counting Instances of Sick

Was running in compatibility mode!!! Sorry

Thanks that has worked, my data was wrong

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1