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

 

[Aladin Akyurek]

F2 houses a month/year of interest like 1-Jul-15.

G1: a role-type of interest.

G2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(UserRange<>"",IF(DateRange-DAY(DateRange)+1=$F2,
    IF(RTypeRange=G$1,MATCH(UserRange,UserRange,0)))),
    ROW(UserRange)-ROW(INDEX(UserRange,1,1))+1),1))

 

[mgregory]

Thanks Aladin,

I have tried this but it has returned zero. Ought the date F2 be a date range or a particular date?

Mike

 

[Aladin Akyurek]

Thanks Aladin,

I have tried this but it has returned zero. Ought the date F2 be a date range or a particular date?

Mike

F2 = 1-Jul-15, which is a particular date, in fact a first day date as required.

Did you the ranges the formula refers to correctly map to the ranges of your lay-out? Did you apply control+shift+enter as instructed?

 

[mgregory]

F2 = 1-Jul-15, which is a particular date, in fact a first day date as required.

Did you the ranges the formula refers to correctly map to the ranges of your lay-out? Did you apply control+shift+enter as instructed?

Thank you Again,

This how the formula looks at the moment and is returning 0. I did apply ctlr shift enter

{=SUM(IF(FREQUENCY(B:B<>"",IF(A:A-DAY(A:A)+1=$F2,IF(D:D=F$27,MATCH(B:B,B:B,0)))),ROW(B:B)-ROW(INDEX(B:B,1,1))+1),1)}

 

[Aladin Akyurek]

Thank you Again,

This how the formula looks at the moment and is returning 0. I did apply ctlr shift enter

{=SUM(IF(FREQUENCY(B:B<>"",IF(A:A-DAY(A:A)+1=$F2,IF(D:D=F$27,MATCH(B:B,B:B,0)))),ROW(B:B)-ROW(INDEX(B:B,1,1))+1),1)}

Do not use whole columns in such columns for reasons of efficiency. Also, you missed an IF...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$400<>"",
    IF($A$2:$A$400-DAY($A$2:$A$400)+1=$F2,
    IF($D$2:$D$400=F$27,MATCH($B$2:$B$400,$B$2:$B$400,0)))),
    ROW($B$2:$B$400)-ROW(INDEX($B$2:$B$400,1,1))+1),1))

 

[mgregory]

Do not use whole columns in such columns for reasons of efficiency. Also, you missed an IF...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$400<>"",
    IF($A$2:$A$400-DAY($A$2:$A$400)+1=$F2,
    IF($D$2:$D$400=F$27,MATCH($B$2:$B$400,$B$2:$B$400,0)))),
    ROW($B$2:$B$400)-ROW(INDEX($B$2:$B$400,1,1))+1),1))

Again thank you.

It is still returning 0

{=SUM(IF(FREQUENCY(IF($B$1:$B$144550<>"",IF($A$1:$A$144550-DAY($A$1:$A$144550)+1=$F2,IF($D$1:$D$144550=F$27,MATCH($B$1:$B$144550,$B$1:$B$144550,0)))),ROW($B$1:$B$144550)-ROW(INDEX($B$1:$B$144550,1,1))+1),1))}

This is the date and format of F2 : "1/08/2015 0:00" which as the same as the dates in column A. Might this be the problem?

2/09/2015 21:05	214	Year 8 Students	student
2/09/2015 20:57	837	Year 8 Students	student
2/09/2015 20:54	672	Parents	parent
2/09/2015 20:54	672	Parents	parent
2/09/2015 20:46	773	Parents	parent
2/09/2015 20:44	272	Year 9 Students	student
2/09/2015 20:43	385	Year 7 Students	student
2/09/2015 20:43	385	Year 7 Students	student
2/09/2015 20:43	385	Year 7 Students	student
2/09/2015 20:43	385	Year 7 Students	student
2/09/2015 20:43	385	Year 7 Students	student

<tbody>
</tbody>

 

[Aladin Akyurek]

Again thank you.

It is still returning 0

{=SUM(IF(FREQUENCY(IF($B$1:$B$144550<>"",IF($A$1:$A$144550-DAY($A$1:$A$144550)+1=$F2,IF($D$1:$D$144550=F$27,MATCH($B$1:$B$144550,$B$1:$B$144550,0)))),ROW($B$1:$B$144550)-ROW(INDEX($B$1:$B$144550,1,1))+1),1))}

This is the date and format of F2 : "1/08/2015 0:00" which as the same as the dates in column A. Might this be the problem?

2/09/2015 21:05	214	Year 8 Students	student
2/09/2015 20:57	837	Year 8 Students	student
2/09/2015 20:54	672	Parents	parent
2/09/2015 20:54	672	Parents	parent
2/09/2015 20:46	773	Parents	parent
2/09/2015 20:44	272	Year 9 Students	student
2/09/2015 20:43	385	Year 7 Students	student
2/09/2015 20:43	385	Year 7 Students	student
2/09/2015 20:43	385	Year 7 Students	student
2/09/2015 20:43	385	Year 7 Students	student
2/09/2015 20:43	385	Year 7 Students	student

<tbody>
</tbody>

Are your ranges really 144550 rows big?

And what is the name of the sheet housing this data?

 

[mgregory]

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

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

 

[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)"

Column B (user id's) are numeric, is it not?