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

Question

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,

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

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

Answer 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

Answer 3

say Profile is in column H code formula in column I2 "=IF(OR(H2="tEACHERS",H2=sTAFF",h2="Admin")),1,0) - this flags the entries you want with 1, 0 otherwise. sum column I for total