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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Id
[/TD]
[TD]Date of Action
[/TD]
[TD]User Name
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]01/01/2015
[/TD]
[TD]Ted
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]01/01/2015
[/TD]
[TD]Mark
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]03/01/2015
[/TD]
[TD]Steven
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]04/01/2015
[/TD]
[TD]Janet
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]04/01/2015
[/TD]
[TD]Steven
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]01/01/2015
[/TD]
[TD]Steven
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]04/01/2015
[/TD]
[TD]Janet
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]03/01/2015
[/TD]
[TD]Mark
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]01/01/2015
[/TD]
[TD]Ted
[/TD]
[/TR]
</tbody>[/TABLE]


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!