+ Reply to Thread
Results 1 to 5 of 5

Formula t count number of employees for each month

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Formula t count number of employees for each month

    Hello:

    Please refer to attached sheet.
    I have payroll info as shown.
    I need formula in cell H2 to S2 to give unique employees for given month.

    Please let me know if you have any questions.
    Thanks.

    Riz
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Formula t count number of employees for each month

    Hi - you want to count the number of employees in each month? 36 in Jan, 43 in Feb,etc.

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula t count number of employees for each month

    Please select try at H2

    =COUNT(1/FREQUENCY(IF($A$2:$A$410>=--(1&H1),IF($A$2:$A$410<EDATE(1&H1,1),MATCH($B$2:$B$410,$B$2:$B$410,))),ROW($A$2:$E$410)-ROW($A$1)))

    or

    =COUNT(1/FREQUENCY(IF($D$2:$D$410=H1,MATCH($B$2:$B$410,$B$2:$B$410,)),ROW($A$2:$E$410)-ROW($A$1)))

    Confirm by Ctrl+Shift+Enter


    or Normal Enter but calculate slower than FREQUENCY
    select H2:S2
    enter formula
    =SUMPRODUCT((Table1[Month]=H1)/COUNTIFS(Table1[Name],Table1[Name],Table1[Month],Table1[Month]))

    Press Ctrl+Enter
    Attached Files Attached Files
    Last edited by Bo_Ry; 10-07-2020 at 03:05 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula t count number of employees for each month

    Hello Bo_Ry

    Very complicated formula but it works

    Thanks a lot

    Roz

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Formula t count number of employees for each month

    Riz,

    Power Query Solution

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    A
    B
    1
    Month Total By Month
    2
    Jan
    23
    3
    Feb
    23
    4
    Mar
    25
    5
    Apr
    23
    6
    May
    25
    7
    Jun
    26
    8
    Jul
    26
    9
    Aug
    24
    10
    Sep
    23
    Sheet: Sheet2
    Last edited by alansidman; 10-07-2020 at 05:35 PM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. [SOLVED] Formula to count number of employees staffed per 30 min interval
    By dbarton0231 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-17-2022, 12:59 PM
  2. Replies: 2
    Last Post: 08-03-2020, 10:10 AM
  3. [SOLVED] Count number of employees attending lectures in each week per month
    By leprince2007 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-02-2018, 02:38 AM
  4. Count Number of Employees Per Month Using Pivot Table Excel 2010
    By mclarke in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-26-2017, 08:56 AM
  5. Count Number of Employees Per Month Using Pivot Table Excel 2010
    By mclarke in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-25-2017, 04:21 PM
  6. [SOLVED] count how many employees were termed in less than 90 days per Month
    By Granny in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-06-2016, 05:43 PM
  7. PIVOT - Number of employees per month
    By obionenairobi in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-26-2015, 08:39 AM

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