Microsoft Excel - Count the number of values each name has in a single column
Solution 1
To use a Pivot Table:
Import your data into an Excel workbook and add the column titles if they are missing.
Select a cell in the data and then click Insert
-> PivotTable
. A Create PivotTable dialog box will display. Make sure it correctly selected your entire range and click OK
.
It will add a new sheet to your workbook with the PivotTable Field List displayed on the right. (If it is missing, click the Show Field List
button on the PivotTable Tools
Options
tab.)
Drag fields to the appropriate part of the pivot table. i.e.
- Profile field to
Report Filter
- User Login to
Row Lables
- Date to
Values
(After you move it, if it doesn't saysCount of Date
, then click the drop-down arrow, selectValue Field Settings
and changeSummarize Values By
toCount
.)
That will give you a pivot table that looks like this.
To see a count for each Page Accessed, drag that field to Column Labels
.
Feel free to explore and play with the PivotTable. Move things around, add more fields to Row Labels
or Column Labels
etc. If you can't figure out how to do a specific thing, ask another question. :-)
Solution 2
Without really seeing how it's set up and showing you how to do a pivot table, this formula will count for you.
=SUM(COUNTIFS(H:H,"cgi",G:G, {"administrator","staff","teachers"}))
You could also select the data go to insert - pivot table
and put column H
as row labels
and column g
as column labels
and put column g
in the sum of values
field, but change it to count
instead of sum
by clicking and selecting value field settings
Related videos on Youtube
turbonerd
Updated on September 18, 2022Comments
-
turbonerd over 1 year
I have a CSV file containing 100,000 rows.
Each row consists of information under a number of columns, such as "Date", "Page Accessed", "Page Type", "IP", "User Surname", "User Forename", "User Login", "Profile".
A few example rows may therefore look like:
A B C D E F G H 18:48:42 Home Drive web page 90.90.90.90 Gill C cgi Teachers 20:48:42 Dashboard web page 90.90.90.90 James R rj Teachers 22:48:42 Shared Drive web page 90.90.90.90 Gill C cgi Teachers
These rows are an individual hit on our Virtual Learning Environment.
I would like to determine how many times each user has made a hit on our VLE.
As such, I would imagine that I need to get excel to count the number of times each
User Login
appears in the list of rows whereTeachers
,Staff
orAdministrator
is the value for "Profile".What's the easiest way of going about this?
I'm using Microsoft Excel 2010.
Thanks in advance,
-
Raystafarian about 12 yearsPivot Table or advanced filtering would do exactly this
-
turbonerd about 12 yearsOK, that's great news, but I have no idea how to do either... :)
-
Raystafarian about 12 yearswhat version of excel are you using
-
turbonerd about 12 yearsI'm using Microsoft Excel 2010. I'll update my OP to clarify that!
-
Raystafarian about 12 yearsYou want to know how many unique values in column G appear when column H reads Teachers OR Staff OR Administrator?
-
turbonerd about 12 yearsI want to know how many times "cgi" under the "User Login" (which, say, is Column H) appears where the "User Profile" (say, column G) is equal to "Teachers" OR "Staff" OR "Administrator"
-
-
turbonerd about 12 yearsThis won't give me a running total of the number of hits each member of staff has made will it?
-
Raystafarian about 12 yearsWe need to count column G as well
-
turbonerd about 12 yearsIncredible post, many thanks! :) I'll look at this when I get to work tomorrow and let you know how I get on.