Count Distinct Values within a date range

[Cheyra90]

Hi All,

I have a problem, the task is to be able to count the amount of unique users logged onto a system within a variable date range (To be changed by the user accessing the spreadsheet) I have all the data in a spreadsheet currently and am able to get the count of unique users by using the following.

The data sits currently as follows:

Id	Date of Action	User Name
1	01/01/2015	Ted
2	01/01/2015	Mark
3	03/01/2015	Steven
4	04/01/2015	Janet
5	04/01/2015	Steven
6	01/01/2015	Steven
7	04/01/2015	Janet
8	03/01/2015	Mark
9	01/01/2015	Ted

<tbody>
</tbody>

The desired output for the above from 01/01/2015 - 02/01/2015 would be 3

=SUMPRODUCT((Sheet1!F2:F50000<>"")/COUNTIF(Sheet1!F2:F50000,Sheet1!F2:F50000&"")) ---- (this gets me a count of all unique users)

I'm now stuck as i am unsure how to filter by a date range in this function.

Any help would be massively appreciated. I'm thinking that using a VBA function/Macro would be the best route but i'm not sure how exactly i'd write it out.

Thank you,

 

[Weazel]

maybe something like...

=SUM(IF(FREQUENCY(IF(F2:F10>="1/1/2015"+0,IF(F2:F10<="1/2/2015"+0,MATCH(G2:G10,G2:G10,0))),ROW(F2:F10)-ROW(F2)+1),1)) Control Shift Enter

Where column F are your dates and column G are the names

Also consider using cell references for the date ranges instead of hard coding them like I did here

 

[Cheyra90]

Thanks so much,

that worked perfectly, I wish I could grasp what it was doing but i'm happy that it works!