# Formula Needed for Counting Users within a Specific Date Range

#### rac241985

##### New Member
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,

### 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
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

</tbody>
Sheet1

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

</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)))}

</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
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
Sure! Take these 2 formulas:

DE
75/1/20175/31/2017

</tbody>
Sheet3

Worksheet Formulas
CellFormula
D7=EOMONTH(TODAY(),-1)+1
E7=EOMONTH(TODAY(),0)

</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.

#### rac241985

##### New Member
Eric you rock!! Thanks once again !

Replies
18
Views
342
Replies
3
Views
102
Replies
0
Views
177
Replies
4
Views
267
Replies
1
Views
35

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.

### 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