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.

Excel count occurrences : List of values with multiple occurrences
List of values with multiple occurrences

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 :

Excel count occurrences : Creation of a pivot table count occurrences
Creation of a pivot table count occurrences

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

Excel count occurrences : Options to create pivot table and count unique values
Options to create pivot table and count unique values

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.

Excel count occurrences : Blank pivot table number of occurrences
Blank pivot table number of occurrences

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

Excel count occurrences : Row selection drag and drop to count occurrences in Excel
Row selection drag and drop to count occurrences in Excel

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.

Excel count occurrences : Value selection drag and drop
Value selection drag and drop

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 count occurrences : Value type selection and Excel pivot table count result
Value type selection and Excel pivot table count result

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.

Excel count occurrences : Copy data in a new column and apply Remove Duplicates
Copy data in a new column and apply Remove Duplicates

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

Excel count occurrences : Remove Duplicates options
Remove Duplicates options

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

Excel count occurrences : Remove Duplicates operation result summary
Remove Duplicates operation result summary

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])
Excel count occurrences : Creation of a countif formula on entries and values
Creation of a countif formula on entries and values

How to count occurrences in Excel

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

Excel count occurrences : Extension of the countif formula for all values to count number of occurrences Excel
Extension of the countif formula for all values to count number of occurrences Excel

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

Excel count occurrences : Unique values with number of occurrences in Excel
Unique values with number of occurrences in Excel

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.

Similar articles

Comments (0)

Leave a comment