Home > database >  Counting the frequency of unique entries in an excel column
Counting the frequency of unique entries in an excel column

Time:10-15

I run a customer service department (I'm new to this so need to learn quickly) and I'm trying to get some analytics on our customer service requests. I have a table in excel documenting every individual service request and I would like a graph showing the total number of requests per customer. Table 1

I could write this manually (see below) but I would like it to automatically update as more service requests are entered.

Example table

I have tried looking for answers to this but can't find any that update automatically or that don't require a table elsewhere to be manually updated with each new customer. We often get new customers and I dont want to have to go back to this every time to update a reference table or customer list.

Surely there must be a way in excel to "Count all entries of each occurrence of a name in a column" in some kind of smart way?

I would really appreciate help with this! Thanks in advance!

CodePudding user response:

Better to use pivot tables.

If below is your data,

enter image description here

  • Go to Insert > PivotTable

    • A popup will show like below

      enter image description here

    • Specify the Table/Range

    • Specify also the Location (anywhere in the Existing Worksheet)

    • The view below will be displayed

      enter image description here

  • Click the Customer checkbox and also drag the Customer field to fill the VALUES box as shown in the image below

    enter image description here

    • You desired table will be displayed as below

      enter image description here

Please vote up if okay.

CodePudding user response:

If you have 365 you could take advantage of spilled ranges.

  • On your second image put =UNIQUE(Table1[Customer]) in cell B3.
  • Put =COUNTIF(Table1[Customer],B3#) in cell C3 - this formula will spill to all your unique customers.

Create two names ranges:

  • One called XAxis which references =Sheet1!$B$3#
  • One called ChartValues which references =Sheet1!$C$3#

Create your chart:

  • Set series value to =Sheet1!ChartValues
  • Set axis to =Sheet1!XAxis

Change Sheet1 to whatever your sheet is called remembering to wrap with ' if it has a space in the name.

CodePudding user response:

Thank you everyone for your suggestions. I found a solution from multiple answers and comments.

I ended up going to my original table that I am using to create new service records. I added a column called "1" and just entered the number 1 in that column, for every row. I then "Hid" this column in the usual fashion.

I then created a pivot table from this table and selected to display columns "Customer" and "1" and used these columns to produce a bar graph. Here is the result (With the column "1" unhidden):

enter image description here

tl;dr: Excel is unable to do this in any smart way, create a column in the original table contianing the number 1 so when a pivot table is made, it will auto sum the 1's for entries from the same customer. Create a plot from this.

It disgusts me . . . but it works.

  • Related