Formula Needed for Counting Users within a Specific Date Range

[rac241985]

Hi I have the following data and need to get a count of how many times a user has attended the lab within a specific date range. Can someone help me out with this please? As an example, how can I get the count of 3 if I select the date range of 4/3/2017 to 4/8/2017. I will have the user input the date range in separate columns. Any help will be appreciated.

Date	User ID
4/1/2017	Dave
4/1/2017	Julie
4/3/2017	Dave
4/5/2017	Tom
4/5/2017	Dave
4/7/2017	Tom
4/8/2017	Julie
4/8/2017	Dave
4/8/2017	Tom

<tbody>
</tbody>

Thanks,

 

[Eric W]

I'm a little confused as to your requirements:

	A	B	C	D	E	F
1	Date	User ID		Start	End	# of Users
2	4/1/2017	Dave		4/3/2017	4/8/2017	3
3	4/1/2017	Julie
4	4/3/2017	Dave		User	How many times user attended in date range
5	4/5/2017	Tom		Julie	1
6	4/5/2017	Dave
7	4/7/2017	Tom
8	4/8/2017	Julie
9	4/8/2017	Dave
10	4/8/2017	Tom

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E5 =COUNTIFS(A2:A10,">="&D2,A2:A10,"<="&E2,B2:B10,D5) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F2 {=SUM(SIGN(FREQUENCY(IF((A2:A10>=D2)*(A2:A10<=E2),MATCH(B2:B10,B2:B10,0)),ROW(A2:A10)-ROW(A2)+1)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you want to know the number of unique users you had in a given date range, use the formula in F2.

If you want to know how many times a given user attended the lab in a given date range, use the formula in E5.


Hope this helps.

 

[rac241985]

Thanks Eric this worked like a Charm

Do you know if there a way to change the first and last date of the month automatically every first of the month so that I do not need to update the range ?

When a user open this excel sheet in June can it be automatically updated to reflect the first and last day of the month?

Any help will be appreciated.

Thanks once again!

 

[Eric W]

Sure! Take these 2 formulas:

	D	E
7	5/1/2017	5/31/2017

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula D7 =EOMONTH(TODAY(),-1)+1 E7 =EOMONTH(TODAY(),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The first will always return the first day of the current month, the second will always return the last day of the current month. Make sure that the cells are formatted as a date.

Glad to help!

 

[rac241985]

Eric you rock!! Thanks once again !