Count unique entries within a date range

**hutto** · 07-03-2013, 09:36 PM

Hi.

I have been trying many many different variations of formulas and have not been able to successfully achieve what I want to do. Basically, I have a worksheet ("sheet 1" in the attached) with two columns, one is Employee numbers, the other is the date they worked. There may be multiple entries of the same employee for the same day, so I want to count the number of unique employees during any particular date range- this date range is usually a block of one week or so, and are referenced in another worksheet ("Calculation Sheet" in the attached). I have attached a sample worksheet, so if anyone has a solution, I'd be most grateful! Basically, I need to answer "How many employees worked between 12/9/2010 and 19/9/2010" where the date range changes.

Thanks in anticipation...

**Tony Valko** · 07-03-2013, 09:56 PM

I didn't look at your file.

Try something like this...

A2:A20 = Employee IDs
B2:B20 = dates

Use cells to hold the date criteria:

D2 = 12/9/2010
E2 = 19/9/2010

Then, this array formula entered in F2:

=SUM(IF(FREQUENCY(IF(B2:B20>=D2,IF(B2:B20<=E2,MATCH(A2:A20,A2:A20,0))),ROW(A2:A20)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**Max, Singapore** · 07-03-2013, 10:06 PM

Think you could try this array formula method ...
1. First, switch the calc mode to Manual (a good precaution as the calcs are quite intensive, and your source range is large - 60k+)
2. Rename your Sheet1 as x
3. Then in your calculation sheet,
Put this into B3, confirm the formula by pressing CTRL+SHIFT+ENTER (instead of just ENTER):
=SUM(IF(FREQUENCY(IF((x!$B$2:$B$60685>A3-7)*(x!$B$2:$B$60685<=A3),IF(x!$A$2:$A$60685<>"",MATCH("~"&x!$A$2:$A$60685,x!$A$2:$A$60685&"",0))),ROW(x!$A$2:$A$60685)-MIN(ROW(x!$A$2:$A$60685))+1)>0,1))
Copy B3 down. Press F9 to recalc (it'll take a while to compute)
----------------------------------------------------
Success? Celebrate it, click the little star at the bottom left of my responses

**Ron Coderre** · 07-03-2013, 10:13 PM

If there are no deductions for ugly formulas...
Using your posted workbook:
This regular formula returns the count of unique Sheet1 employees who worked in the week ended on the date in Col_A

Formula:

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

**hutto** · 07-03-2013, 10:24 PM

Thanks Biff- I think I have tried that in the past but came unstuck as I am also using dynamically named ranges. It gets complicated for me when I get to your ROW(A2)+1 entry... I should have mentioned that:
A2:A20 = Employee IDs- I have a Named range called "Names"
B2:B20 = dates- I have a named range called "Dates"

I tried the formula:
=SUM(IF(FREQUENCY(IF(Date>'Calculation Sheet'!A148,IF(Date<='Calculation Sheet'!A149,MATCH(Names,Names,0))),ROW(Names)-ROW('Calculation Sheet'!A3)+1),1))

But it returns 1, when this is not the case...

Any other suggestions?

Originally Posted by Tony Valko

I didn't look at your file.

Try something like this...

A2:A20 = Employee IDs
B2:B20 = dates

Use cells to hold the date criteria:

D2 = 12/9/2010
E2 = 19/9/2010

Then, this array formula entered in F2:

=SUM(IF(FREQUENCY(IF(B2:B20>=D2,IF(B2:B20<=E2,MATCH(A2:A20,A2:A20,0))),ROW(A2:A20)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**AlKey** · 07-03-2013, 10:31 PM

You could use remove duplicates feature first. Highlight both columns, go to Data tab and click on remove duplicates. Then insert Pivot Table to calculate all unique values.

**Tony Valko** · 07-04-2013, 09:27 AM

With dynamic named ranges...

Replace:

-ROW('Calculation Sheet'!A3)+1

With:

-MIN(ROW(Names))+1

Make sure you enter the formula as an array formula.

Array formulas are entered differently than a regular formula.
After you type in a regular formula you hit the ENTER key.
With an array formula you *must* use a combination of keys.
Those keys are the CTRL key, the SHIFT key and the ENTER key.
That is, hold down both the CTRL key and the SHIFT key then
hit the ENTER key.

When done properly Excel will enclose the formula in squiggly
brackets { }. You can't just type these brackets in, you
*must* use the key combo to produce them. Also, anytime
you edit an array formula it *must* be re-entered as an
array using the key combo.