Calculating Number of Unique Values in Column

[mgregory]

Hello,
I am preparing a usage report for a school's Learning Management System. I have downloaded the raw data of each login into an Excel file. Each row down the worksheet represents one login. Each column across the page represents some identifying information about the user. These columns include; unique User ID; Date of the login, Time of the login, Role type (Staff, Parent, Student).

I have have found this formula to calculate the the number of unique logins =SUM(IF(FREQUENCY($C:$C,$C:$C)>0,1)). Of the nearly 155 000 logins, they have been made by 748 unique users. Excellent...I think that it is accurate.

Furthermore I need an extension of that formula to calculate the number Unique users based on the month and the role type.

Column A: Date
Column B: User ID
Column C: Sub Role-type
Column D: Role-type

Many Thanks

Mike

 

[mgregory]

Yes they are? You are correct.

 

[Aladin Akyurek]

Yes. I am afraid that there are this number of rows...perhaps this is problem...

The name of the sheet is "report-login (1)"

Yes they are? You are correct.

Define Lrow via Formulas | Name Manager as referring to:

 =MATCH(9.99999999999999E+307,'report-login (1)'!$A:$A)

Define Dates as referring to:

 ='report-login (1)'!$A$1:INDEX('report-login (1)'!$A:$A,Lrow)

Define Users as referring to:

 ='report-login (1)'!$B$1:INDEX('report-login (1)'!$B:$B,Lrow)

Define SubRoles as referring to:

 ='report-login (1)'!$C$1:INDEX('report-login (1)'!$C:$C,Lrow)

Define Roles as referring to:

 ='report-login (1)'!$D$1:INDEX('report-login (1)'!$D:$D,Lrow)

Now invoke: Control+shift+enter...

=SUM(IF(FREQUENCY(IF(ISNUMBER(Users),
    IF(Dates-DAY(Dates)+1=$F2,IF(Roles=F$27,Users))),Users),1))

 

[mgregory]

Thanks Again,

Still returning 0 could it be the date F2 formatted "1/08/2015 12:00:00 AM" Maybe there are no logins for that precise date and time. I will try reformatting it. Will the formula look for just this date or the whole month?

Mike

 

[Aladin Akyurek]

Thanks Again,

Still returning 0 could it be the date F2 formatted "1/08/2015 12:00:00 AM" Maybe there are no logins for that precise date and time. I will try reformatting it. Will the formula look for just this date or the whole month?

Mike

The time bit...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(Users),
    IF(INT(Dates-DAY(Dates)+1)=INT($F2),IF(Roles=F$27,Users))),Users),1))

Without the time bits (without the INT calls), the formula would run faster...