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

1. ## 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!

2. ## 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.

3. ## 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. ## 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!

5. ## 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. ## 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. ## 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!

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