+ Reply to Thread
Results 1 to 7 of 7

Formula to count unique names within many different date ranges for headcount calculation

  1. #1
    Registered User
    Join Date
    02-25-2014
    Location
    Burnaby, BC
    MS-Off Ver
    Excel 2010
    Posts
    29

    Formula to count unique names within many different date ranges for headcount calculation

    Hi Everyone:

    I have a huge data set of timesheet entries, and I want to calculate the employee headcount in each week. I've attached a simplified example. Each entry has data for the "Week #" and "Week Of". I need a formula that will look at either the "Week #" or "Week Of", and figure out how many unique employees there are within each period. I can gather the totals in a pivot table, so if the formula could count only one occurrence of each name for each pay period, and ignore the rest of the occurrences, that would be great. Maybe mark the first occurrence with a "1" and the rest with a "0" in each period?

    Any help would be greatly appreciated.

    Thanks!
    Attached Files Attached Files
    Last edited by PWM; 05-12-2014 at 09:19 PM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Formula to count unique names within many different date ranges for headcount calculat

    hi there. try this array formula:
    =SUM(IF(FREQUENCY(IF($B$2:$B$151=1,MATCH($A$2:$A$151,$A$2:$A$151,0)),ROW($A$2:$A$151)-ROW($A$2)+1),1))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    you may change the one in red to a cell reference instead.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    02-25-2014
    Location
    Burnaby, BC
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Formula to count unique names within many different date ranges for headcount calculat

    Hi benishiryo - thanks for your reply. Your formula is well beyond my skills! However, when I enter the formula in the example doc the formula correctly sums up the headcount for the first period (Week Of 12/30/2013) as "7", but it then gives that same result for the other 2 periods as well - weeks of 1/6/2014 and 1/13/2014. Those pay periods have different head counts in my example doc. In other words, the formula doesn't provide separate calculations for each unique period. It just seems to work just for the first period. I made sure to enter the array formula correctly. Any further thoughts you have would be appreciated.

    Thank you!

  4. #4
    Registered User
    Join Date
    02-25-2014
    Location
    Burnaby, BC
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Formula to count unique names within many different date ranges for headcount calculat

    Hi benishiryo:

    Thanks for your replies. I've attached a new doc that includes the desired result I'm looking for. Ideally, it will count each unique name in each week # range as "1", and any other instances of the name in the same range as "0". That way I can use a "Count" to determine the total overall headcount or the dept headcount in each week # period. Your help is much appreciated!

    Thanks!
    Attached Files Attached Files

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Formula to count unique names within many different date ranges for headcount calculat

    try this in G2:
    =--(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1)

    do not make this part of the Table.

  6. #6
    Registered User
    Join Date
    02-25-2014
    Location
    Burnaby, BC
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Formula to count unique names within many different date ranges for headcount calculat

    Hi benishiryo:

    Thanks again for your help. The last formula does what I want, but it brings my computer to its knees, and the file often crashes. I'm working with almost 200,000 rows of data, with about 15 columns, and the file is around 50MB. There are several array formulas in there, so I've already got performance issues with the file. When I add yours, it's just too much. I'm trying to get it to calculate once on a stripped down version of the file so I can then paste the results into my actual column as data with no formulas. I'll need to do this each time I add new data to the file, but it will only be necessary for the data I'm adding - I won't need to recalculate the entire file again. I'm hoping this will work for me. I would like the data to reside in the table so that it's available to use in my pivot tables and charts. Is there a reason why you suggest not adding it to the table itself? I haven't tried that, but I'm curious. I don't understand how your formula works since I'm not that advanced using Excel.

    Thanks,
    Patrick

  7. #7
    Registered User
    Join Date
    02-25-2014
    Location
    Burnaby, BC
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Formula to count unique names within many different date ranges for headcount calculat

    Hi benishiryo:

    Funny you should mention PowerPivot - I just became aware of it last week, and now have it installed. Haven't really worked with it too much yet. In the meantime, I've been copying and pasting the results of my array formulas as values, which has helped quite a bit with the performance problem.

    I'll marked this thread closed for now. Thanks again!

+ 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. Replies: 1
    Last Post: 07-17-2013, 11:34 AM
  2. Formula for Date/Time count across multiple ranges
    By nigo75 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2013, 09:24 PM
  3. [SOLVED] count indifferent names between date ranges
    By keith6292 in forum Excel General
    Replies: 5
    Last Post: 01-16-2013, 05:38 PM
  4. Replies: 1
    Last Post: 05-14-2012, 10:14 AM
  5. Excel 2007 : Headcount unique names
    By daniel_t in forum Excel General
    Replies: 2
    Last Post: 01-31-2011, 07:10 AM

Tags for this Thread

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