Calculating Number of Unique Values in Column

mgregory

New Member
Joined
Sep 3, 2015
Messages
7
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
F2 houses a month/year of interest like 1-Jul-15.

G1: a role-type of interest.

G2, control+shift+enter, not just enter:
Rich (BB code):

=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))
 
Upvote 0
Thanks Aladin,

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

Mike
 
Upvote 0
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?
 
Upvote 0
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)}
 
Upvote 0
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:

Rich (BB code):

=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))
 
Upvote 0
Do not use whole columns in such columns for reasons of efficiency. Also, you missed an IF...

Control+shift+enter, not just enter:

Rich (BB code):

=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?

[TABLE="width: 348"]
<tbody>[TR]
[TD]2/09/2015 21:05
[/TD]
[TD]214
[/TD]
[TD]Year 8 Students
[/TD]
[TD]student
[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:57
[/TD]
[TD]837
[/TD]
[TD]Year 8 Students
[/TD]
[TD]student
[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:54
[/TD]
[TD]672
[/TD]
[TD]Parents
[/TD]
[TD]parent
[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:54
[/TD]
[TD]672
[/TD]
[TD]Parents
[/TD]
[TD]parent
[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:46
[/TD]
[TD]773
[/TD]
[TD]Parents
[/TD]
[TD]parent
[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:44
[/TD]
[TD]272
[/TD]
[TD]Year 9 Students
[/TD]
[TD]student
[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:43
[/TD]
[TD]385
[/TD]
[TD]Year 7 Students
[/TD]
[TD]student
[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:43
[/TD]
[TD]385
[/TD]
[TD]Year 7 Students
[/TD]
[TD]student
[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:43
[/TD]
[TD]385
[/TD]
[TD]Year 7 Students
[/TD]
[TD]student
[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:43
[/TD]
[TD]385
[/TD]
[TD]Year 7 Students
[/TD]
[TD]student
[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:43
[/TD]
[TD]385
[/TD]
[TD]Year 7 Students
[/TD]
[TD]student
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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?

[TABLE="width: 348"]
<tbody>[TR]
[TD]2/09/2015 21:05[/TD]
[TD]214[/TD]
[TD]Year 8 Students[/TD]
[TD]student[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:57[/TD]
[TD]837[/TD]
[TD]Year 8 Students[/TD]
[TD]student[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:54[/TD]
[TD]672[/TD]
[TD]Parents[/TD]
[TD]parent[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:54[/TD]
[TD]672[/TD]
[TD]Parents[/TD]
[TD]parent[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:46[/TD]
[TD]773[/TD]
[TD]Parents[/TD]
[TD]parent[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:44[/TD]
[TD]272[/TD]
[TD]Year 9 Students[/TD]
[TD]student[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:43[/TD]
[TD]385[/TD]
[TD]Year 7 Students[/TD]
[TD]student[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:43[/TD]
[TD]385[/TD]
[TD]Year 7 Students[/TD]
[TD]student[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:43[/TD]
[TD]385[/TD]
[TD]Year 7 Students[/TD]
[TD]student[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:43[/TD]
[TD]385[/TD]
[TD]Year 7 Students[/TD]
[TD]student[/TD]
[/TR]
[TR]
[TD]2/09/2015 20:43[/TD]
[TD]385[/TD]
[TD]Year 7 Students[/TD]
[TD]student[/TD]
[/TR]
</tbody>[/TABLE]

Are your ranges really 144550 rows big?

And what is the name of the sheet housing this data?
 
Last edited:
Upvote 0
Yes. I am afraid that there are this number of rows...perhaps this is problem...

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

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top