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.


[TABLE="width: 150"]
<tbody>[TR]
[TD]Date[/TD]
[TD]User ID[/TD]
[/TR]
[TR]
[TD]4/1/2017[/TD]
[TD]Dave[/TD]
[/TR]
[TR]
[TD]4/1/2017[/TD]
[TD]Julie[/TD]
[/TR]
[TR]
[TD]4/3/2017[/TD]
[TD]Dave[/TD]
[/TR]
[TR]
[TD]4/5/2017[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]4/5/2017[/TD]
[TD]Dave[/TD]
[/TR]
[TR]
[TD]4/7/2017[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]4/8/2017[/TD]
[TD]Julie[/TD]
[/TR]
[TR]
[TD]4/8/2017[/TD]
[TD]Dave[/TD]
[/TR]
[TR]
[TD]4/8/2017[/TD]
[TD]Tom[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,

 

[Eric W]

I'm a little confused as to your requirements:

	A	B	C	D	E	F
Start	End	# of Users
User	How many times user attended in date range
Julie

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Date[/TD]
[TD="bgcolor: #FAFAFA"]User ID[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/1/2017[/TD]
[TD="bgcolor: #FAFAFA"]Dave[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/3/2017[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/8/2017[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/1/2017[/TD]
[TD="bgcolor: #FAFAFA"]Julie[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/3/2017[/TD]
[TD="bgcolor: #FAFAFA"]Dave[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]Tom[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/5/2017[/TD]
[TD="bgcolor: #FAFAFA"]Dave[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/7/2017[/TD]
[TD="bgcolor: #FAFAFA"]Tom[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/8/2017[/TD]
[TD="bgcolor: #FAFAFA"]Julie[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/8/2017[/TD]
[TD="bgcolor: #FAFAFA"]Dave[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/8/2017[/TD]
[TD="bgcolor: #FAFAFA"]Tom[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]=COUNTIFS(A2:A10,">="&D2,A2:A10,"<="&E2,B2:B10,D5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF((A2:A10>=D2)*(A2:A10<=E2),MATCH(B2:B10,B2:B10,0)),ROW(A2:A10)-ROW(A2)+1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]7[/TD]
[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]5/31/2017[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"]=EOMONTH(TODAY(),-1)+1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=EOMONTH(TODAY(),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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 !