+ Reply to Thread
Results 1 to 7 of 7

Counting Instances of Sick

  1. #1
    Registered User
    Join Date
    03-12-2012
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    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. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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?
    Audere est facere

  3. #3
    Registered User
    Join Date
    03-12-2012
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    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. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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
    Last edited by daddylonglegs; 03-12-2012 at 08:29 AM.

  5. #5
    Registered User
    Join Date
    03-12-2012
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Counting Instances of Sick

    thanks for the prompt reply

    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. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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. #7
    Registered User
    Join Date
    03-12-2012
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Counting Instances of Sick

    Was running in compatibility mode!!! Sorry

    Thanks that has worked, my data was wrong

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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