+ Reply to Thread
Results 1 to 9 of 9

Count number of staff per month, with joiners and leavers

  1. #1
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    Count number of staff per month, with joiners and leavers

    Hello,

    Re the attached, I am looking for a formula that will populate cells H2:S7, and give me the number of staff each month, in each Region (eg Home, Americas...), accounting for new starters and leavers.

    I am trying to use COUNTIFS, SUMPRODUCT, but can't get it to work.

    The yellow cells are examples of the correct final figures.

    I will also need to do this for summing the SAL (col E), presumably I can use the same principles for the count?

    Does anyone have any ideas?

    Thanks
    Rich
    Attached Files Attached Files
    There must be a quicker way...

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count number of staff per month, with joiners and leavers

    Hello

    Are you sure that your expected results(according your sample) are correct?

    What about?

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In H2 and copy down and across?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Count number of staff per month, with joiners and leavers

    Hi Fotis,

    The staff numbers carry forward each month, and only increase / decrease with starters / leavers. Total number of staff employed at the end of the month.

    So the criteria for cell L2, August, Home (for example), would be those rows where col B is April to August, and col C is August to March. ie those staff that had started by August and didn't leave until August or after.

    I hope that makes sense, thanks for your post.

    Thanks
    Rich

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count number of staff per month, with joiners and leavers

    Unfortunately i can't get the logic of your plan. I hope someone else will do. Good luck.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Count number of staff per month, with joiners and leavers

    =SUMPRODUCT((DATEVALUE(1&$B$2:$B$58&((($B$2:$B$58="Jan")+($B$2:$B$58="Feb")+($B$2:$B$58="Mar"))>0)*1+2014)<=DATEVALUE(1&H$1&IF(OR(H$1={"Jan","Feb","Mar"}),1,0)+2014))*(DATEVALUE(1&$C$2:$C$58&((($C$2:$C$58="Jan")+($C$2:$C$58="Feb")+($C$2:$C$58="Mar"))>0)*1+2014)>=DATEVALUE(1&H$1&IF(OR(H$1={"Jan","Feb","Mar"}),1,0)+2014))*($D$2:$D$58=$G2))
    Try this in H2 and copy across
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Count number of staff per month, with joiners and leavers

    Um...wow!

    Not entirely sure what's going on there but I will enjoy trying to figure it out. It works perfectly, and I can adjust it to also sum the SAL for each month.

    Many thanks!!!

    Rich

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Count number of staff per month, with joiners and leavers

    Thanks for your feedback

  8. #8
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Count number of staff per month, with joiners and leavers

    Oh no!

    I have incorporated this into my data, and it is bringing back an error (#VALUE), as there are empty cells in my data (unfortunately nothing I can do about this).

    Please Login or Register  to view this content.
    Any ideas?

    Thanks
    Rich
    Last edited by Ricardo Mass; 11-25-2014 at 11:43 AM.

  9. #9
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Count number of staff per month, with joiners and leavers

    I've tried adding in a ($B$2:$B$58<>"") to the original formula, but still get the error

    I'm not having any luck with changing this to the --() format

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to find staff assessments Date during a given Month?
    By Drackon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-21-2014, 09:50 AM
  2. Count number of networkdays for each month in a multi month period
    By dreddster in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-10-2013, 11:12 AM
  3. Replies: 3
    Last Post: 08-07-2011, 02:30 AM
  4. Which staff member has the most sales in this month?
    By shazzm in forum Excel General
    Replies: 2
    Last Post: 04-28-2011, 03:46 AM
  5. Replies: 0
    Last Post: 11-18-2007, 07:24 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1