# There HAS to be an easier way to do this than what I'm doing

#### anewc1

##### Board Regular
But I am not good enough at functions to write one to do this!

I need to find out the number of unique employee IDs with hire dates between a certain date range. Can anyone help? I want to be able to simply paste a function into a summary spreadsheet that will count how many new hires there were on a particular week.

What I have been doing is to use Auto Filter to show the new hires between the dates I want, and then copying those entries to a new spreadsheet and doing an advanced filter on the employee ID numbers to find the number of unique values.

Can anyone recommend an easier way? Thanks in advance!

employee_id last_name first_name hire_date
071001000000000269 8/14/2006
071001000000000269 8/14/2006
071001000000000269 8/14/2006
071001000000000270 8/14/2006
071001000000000270 8/14/2006
071001000000000270 8/14/2006
071001000000000270 8/14/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000285 8/16/2006
071001000000000288 8/16/2006
071001000000000288 8/16/2006
071001000000000288 8/16/2006
071001000000000288 8/16/2006
071001000000000288 8/16/2006
071001000000000288 8/16/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000290 8/18/2006
071001000000000290 8/18/2006

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Domenic

##### MrExcel MVP
If you download and install the free add-in Morefunc.xll, you can use the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

=COUNTDIFF(IF(DateRange>=StartDate,IF(DateRange<=EndDate,IF(EmpIDRange<>"",EmpIDRange))),,FALSE)

Hope this helps!

#### barry houdini

##### MrExcel MVP
I see Domenic was too quick for me......

If your employee IDs are in A2:A100 and hire dates in D2:D100 then you can use this formula

=COUNT(1/FREQUENCY(IF((D2:D100 >=F2)*(D2:D100< F2+7),MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1))

confirmed with CTRL+SHIFT+ENTER

where F2 contains your week start date

#### Barry Katcher

##### Well-known Member
Data>Filter>Advanced Filter on range \$A\$4:\$B\$18 and with Criteria Range of \$D\$1:\$E\$2 (with Unique Records Only checked) will give you what you want.

EDIT: Ooops - I forgot the Count. The formula in B19 is:
=SUBTOTAL(2,B5:B18)
Book1
ABCDE
1Hire DateHire Date
2>=8/14/2006<=8/15/2006
3
4Employee IDHire Date
50710010000000002698/14/2006
60710010000000002698/14/2006
70710010000000002698/14/2006
80710010000000002708/14/2006
90710010000000002708/14/2006
100710010000000002708/14/2006
110710010000000002708/14/2006
120710010000000002348/16/2006
130710010000000002348/16/2006
140710010000000002348/16/2006
150710010000000002348/16/2006
160710010000000002348/16/2006
170710010000000002348/16/2006
180710010000000002348/16/2006
19Count14
20
21Result:
22Employee IDHire Date
230710010000000002698/14/2006
240710010000000002708/14/2006
25Count2
Sheet1

#### anewc1

##### Board Regular

=COUNTDIFF(IF(I:I>=7/31/2006,IF(I:I<=8/5/2006,IF(A:A<>"",A:A))),,FALSE)

This is what the formula ended up being when I changed it to reflect my ranges, but it returns a value of FALSE, and I need it to return a number. What am I doing wrong?

Thank you again for all your help.

#### Barry Katcher

##### Well-known Member
Ah, the old Formula vs. Menu vs. VBA ploy. Anyone care to contribute the VBA?

#### barry houdini

##### MrExcel MVP

You can't use whole columns, you need to use a specific range like I1:I100, also if you use a date within the formula itself then you need to format differently for it to be recognised as a date. Try

=COUNTDIFF(IF(I1:I100>="7/31/2006"+0,IF(I1:I100<="8/5/2006"+0,IF(A1:A100<>"",A1:A100))),,FALSE)

confirmed with CTRL+SHIFT+ENTER

#### anewc1

##### Board Regular
=COUNT(1/FREQUENCY(IF((I2:I651>=7/31/2006)*(I2:I651<7/31/2006+7),MATCH(A2:A651,A2:A651,0)),ROW(A2:A651)-ROW(A2)+1))

This one didn't work either, it returned a value of zero, and the value should have returned 16. Argh!

What am I doing wrong?

#### anewc1

##### Board Regular
YES!!!

This worked:

=COUNTDIFF(IF(\$I\$2:\$I\$651>="7/31/2006"+0,IF(\$I\$2:\$I\$651<="8/5/2006"+0,IF(\$A\$2:\$A\$651<>"",\$A\$2:\$A\$651))),,FALSE)

I just have to remember to use CTRL+SHIFT+ENTER or it comes back with FALSE.

I can't thank you all enough!!

Replies
3
Views
175
Replies
4
Views
203
Replies
21
Views
478
Replies
3
Views
130
Replies
7
Views
111

1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back