Excel – Microsoft Excel – Count the number of values each name has in a single column

microsoft excelmicrosoft-excel-2010pivot tableworksheet-function

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 where Teachers, Staff or Administrator is the value for "Profile".

What's the easiest way of going about this?

I'm using Microsoft Excel 2010.

Thanks in advance,

Best Answer

To use a Pivot Table:

Import your data into an Excel workbook and add the column titles if they are missing.

picture of data in Excel with column titles

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.)

PivotTable field list

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 says Count of Date, then click the drop-down arrow, select Value Field Settings and change Summarize Values By to Count.)

That will give you a pivot table that looks like this.

finished PivotTable

To see a count for each Page Accessed, drag that field to Column Labels.

altered PivotTable

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. :-)