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:
<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,
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,