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

anewc1

Board Regular
Joined
Sep 8, 2005
Messages
180
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
Joined
Mar 10, 2004
Messages
19,696
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
Joined
Mar 23, 2005
Messages
20,825
I see Domenic was too quick for me...... :)

An alternative, if you can't or won't download Morefunc,....

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
Joined
Feb 25, 2002
Messages
4,053
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
Joined
Sep 8, 2005
Messages
180

ADVERTISEMENT

=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
Joined
Feb 25, 2002
Messages
4,053
Ah, the old Formula vs. Menu vs. VBA ploy. Anyone care to contribute the VBA?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
Joined
Sep 8, 2005
Messages
180
=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
Joined
Sep 8, 2005
Messages
180
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!! :biggrin:
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top