Formula Needed for Counting Users within a Specific Date Range

rac241985

New Member
Joined
Jan 30, 2017
Messages
27
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.


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

<tbody>
</tbody>

Thanks,
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,597
I'm a little confused as to your requirements:

ABCDEF
1DateUser IDStartEnd# of Users
24/1/2017Dave4/3/20174/8/20173
34/1/2017Julie
44/3/2017DaveUserHow many times user attended in date range
54/5/2017TomJulie1
64/5/2017Dave
74/7/2017Tom
84/8/2017Julie
94/8/2017Dave
104/8/2017Tom

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

Worksheet Formulas
CellFormula
E5=COUNTIFS(A2:A10,">="&D2,A2:A10,"<="&E2,B2:B10,D5)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
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

New Member
Joined
Jan 30, 2017
Messages
27
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,597
Sure! Take these 2 formulas:

DE
75/1/20175/31/2017

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

Worksheet Formulas
CellFormula
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! :cool:
 

Forum statistics

Threads
1,186,178
Messages
5,956,392
Members
438,249
Latest member
georgebasalic3

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