+ Reply to Thread
Results 1 to 13 of 13

Help with Counting Number of Sickness/Lateness over a month on Summary

  1. 01-09-2018, 11:45 AM #1
    MrAshy
    MrAshy is offline
    Registered User
    Join Date
    01-09-2018
    Location
    Darlington, England
    MS-Off Ver
    Office 2016
    Posts
    6

    Help with Counting Number of Sickness/Lateness over a month on Summary

    Hey,

    I'm trying to summarise month on month how many time a person is off sick or late. Currently we track our attendance on individual monthly sheets so I am looking at creating a summary page on a central sheet to link to the month sheets but cannot work out the best, and simplest formula for the task.

    we have it set out as per the screen grab:

    Capture.JPG

    In one month there is a potential 24 selling days so for each agent there is 24 SIC columns to count so would a countif be the formula to use but how would it work given there is 24 columns?

    thanks in advance.
    Reply With QuoteRegister To Reply
  2. 01-09-2018, 11:57 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 01-09-2018, 12:09 PM #3
    MrAshy
    MrAshy is offline
    Registered User
    Join Date
    01-09-2018
    Location
    Darlington, England
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    sorry for including a screenshot please see the attached example file. I have used the same format as we have it currently which does include merged cells but I can work around this by fixing certain cells with the formula.

    many thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-09-2018, 12:24 PM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    In C4:
    =SUMPRODUCT((Attendance!$A$4:$A$6=Summary!$B4)*(Attendance!$E$2:$AH$2="Lte")*Attendance!$E$4:$AH$6)

    and in D4:
    =SUMPRODUCT((Attendance!$A$4:$A$6=Summary!$B4)*(Attendance!$E$2:$AH$2="Sick")*Attendance!$E$4:$AH$6)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-09-2018, 12:28 PM #5
    MrAshy
    MrAshy is offline
    Registered User
    Join Date
    01-09-2018
    Location
    Darlington, England
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    Thanks for that. Is there a way for it not to sum but count? so Person 1 = 1 and Person 3 = 2?
    Reply With QuoteRegister To Reply
  6. 01-09-2018, 12:33 PM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    Ooops...

    =SUMPRODUCT((Attendance!$A$4:$A$6=Summary!$B4)*(Attendance!$E$2:$AH$2="Lte")*ISNUMBER(Attendance!$E$4:$AH$6))

    and

    =SUMPRODUCT((Attendance!$A$4:$A$6=Summary!$B4)*(Attendance!$E$2:$AH$2="Sick")*ISNUMBER(Attendance!$E$4:$AH$6))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 01-09-2018, 12:43 PM #7
    MrAshy
    MrAshy is offline
    Registered User
    Join Date
    01-09-2018
    Location
    Darlington, England
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    Brilliant so then if i wanted it over more columns i would amend $AH$6 to say $EE$6 and so on?
    Reply With QuoteRegister To Reply
  8. 01-09-2018, 12:47 PM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    Yep. That's about it...

    You're welcome.


    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Reply With QuoteRegister To Reply
  9. 01-09-2018, 01:04 PM #9
    MrAshy
    MrAshy is offline
    Registered User
    Join Date
    01-09-2018
    Location
    Darlington, England
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    im still struggling to match the LTE column using the formual you provided.
    Can you take a look at the attached and see where i am going wrong - i have removed all personal details.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 01-09-2018, 01:14 PM #10
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,148

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    One change you could add ,,,,

    in HV12

    =SUMIF($E$10:$HP$10,HV$11,$E12:$HP12)

    copy across and down

    Copy to all "tables"
    Reply With QuoteRegister To Reply
  11. 01-09-2018, 01:14 PM #11
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    1. The names didn't match well... no randoms n the results. However the main problem was the range was one column out and unlocked.

    =SUMPRODUCT((Sheet1!$A$12:$A$108=Summary!$B5)*(Sheet1!$E$10:$HP$10="Lte")*ISNUMBER(Sheet1!$E$12:$HP$108))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 01-09-2018, 01:17 PM #12
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,148

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    and ...

    C5 of "Summary"

    =SUMPRODUCT((Sheet1!$A$12:$A$108=Summary!$B5)*(Sheet1!$E$10:$HP$10="Lte")*ISNUMBER(Sheet1!$E$12:$HP$108))

    D5 same change
    Reply With QuoteRegister To Reply
  13. 01-09-2018, 01:29 PM #13
    MrAshy
    MrAshy is offline
    Registered User
    Join Date
    01-09-2018
    Location
    Darlington, England
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Help with Counting Number of Sickness/Lateness over a month on Summary

    Thanks for all your help! Worked in the end.
    Marked thread as solved and left Rep!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sickness Absence by Month
    By JoeMac89 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-02-2017, 05:04 PM
  2. SETUP - Sickness recording using Bradford Factor for 12 month rolling period
    By Pam2501 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-22-2015, 08:47 AM
  3. Calculating Total Sickness in a rolling 12 month Period
    By china in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-08-2015, 08:04 AM
  4. Need help with a lateness tracker.
    By MannyLNJ in forum Excel General
    Replies: 6
    Last Post: 12-30-2014, 12:01 PM
  5. [SOLVED] Counting number of occurrences in a given month
    By tief10 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2014, 02:20 AM
  6. counting the number of times something is bought during a month
    By ldd in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-22-2005, 03:25 PM
  7. Counting number of incidents of a month
    By CD Web in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-18-2005, 06:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1