Excel count occurrences

Quick links

Count occurrences Excel

Having a list of values, and want to know how many times each unique entry is repeated ?

See below two ways to do it : use pivot table to count number of occurrences, or count how many times a value appears in a column Excel with builtin functions.

For this example, we will use a list of city names, and in Excel we will count how many times a value appears in a column.

The column contains multiple occurrences of the city names, as it comes from a list of addresses, in which we extracted the cities. At the end, we will be able to get the percentage of address from each city for example, or to know which city is the most represented in the list, or has the least inhabitants.

Number of occurrences in Excel

The objective is to find the unique occurrences in the list, meaning listing all values but only once, and also to have Excel count occurrences.

As explained above, we have two solutions, which are to use a pivot table count unique values, and to have Excel count occurrences using standard functions.

Both solutions will give us the same result.

Pivot table number of occurrences

Here's a simple solution for Excel count how many times a value appears in a column, using a pivot table.

Start by selecting your list of values, and go to menu INSERT > PivotTable :

Make sure your selection is correct, and eventually select in which sheet you want the pivot table count unique values to be generated.

Here you are, use pivot table to count unique values - in below example with only with one column, which will be used in the pivot table to count the number of occurrences.

Drag and drop the name of the column with the values you want to count to ROWS.

Pivot table count number of occurrences

Repeat the operation, drag and drop the column name to VALUES to get the Excel pivot table count unique.

Make sure the Count of option is selected, and voilà ! You know how many times each unique values appear in your list with the Excel pivot table count number of occurrences. This is how to get the pivot table count rows.

Excel pivot count

It can be used for many different purposes, like in Excel count number of times a word appears, or using a pivot table, count how many times each group name appears in the list, and in Excel count how many times a value appears in a column. It also gives the distinct count in pivot table Excel 2016.

Excel count how many times each value appears in a column

The other solution to Excel count how many times a value appears in a column, is to use built in functions.

Start by copying the list, and pasting it in another column. With the pasted data selected, click on menu option DATA > Remove Duplicates. This will allow you to have one column with all values, and one column with unique values only to allow the Excel count number of occurrences.

In the Remove Duplicates menu, make sure you have selected the right column, and uncheck in case there is no header.

A confirmation message should be displayed, showing how many entries and unique values the list contains

Excel count how many times a value appears

Then, next to the first entry of the unique values, enter the following function, to count how many times it occurs in the full list.

The function countif will do in Excel a count of how many times a value appears in a column. The column has to be given as first argument, and the value to look for as the second argument.

=countif([column with full list];[first value])

How to count occurrences in Excel

See the first result, extend the formula to all unique values to count number of occurrences in Excel.

And voilà ! Here the result is displayed, with number of occurrences in Excel for each value.

It can be used for example to count number of times a word appears in Excel, to do in Excel count number of occurrences in a column, or also to count duplicates in Excel, and of course to find out in Excel how many times a value appears.

Excel pivot table count unique

Another way to count occurrences in Excel, is to use a pivot table to count unique values occurrences in a spreadsheet.

To do so, start by having a table ready in a spreadsheet.

Create a new pivot table by selecting the table with the necessary values, and, in the field list, drag and drop the name of the column with values to count unique in the row labels box, and drap and drop the same column to the values box.

The Excel pivot table will display the count of unique values that way.

Excel count occurrences : Excel pivot table count unique
Excel count occurrences : Excel pivot table count unique

Similar articles

Comments (0)

Leave a comment