How many times are the values in the table?

There is an easy way to find number of occurrences of items in the table. For example, the following table contains repeated Products. We want to know, how many times was each product ordered.

Table with duplicates


A simple and convenient solution of this task offers the Pivot table. Select one of the cell from the list of products and choose Insert PivotTable.

Insert PivotTable


PivotTable can be placed in the new sheet or in the existing sheet. The choice depends on you, it doesn’t matter.

Create new PivotTable menu


Move the item Name into the fields Row Labels and Values. In the Values should be Count of Name. If it is not, just click on the item, select Value Field Settings… and choose Count.

PivotTable Field list - count of products


PivotTable now shows how many times are the products in our source table.

Count of Products


Task 2 / show this for each month

In the source table is the information about Date of Order. We can add this field into the pivot table and group it by months.

PivotTable Field list - count of products by month


PivotTable now looks like this (view by days):

Pivot table with date of order


To change it, to view by months, do left-click on the dates and from the menu select Group… . You can find it also in the top menu section PivotTable Tools-> Options-> Group Selection.

Menu in pivot table


In the Grouping menu choose Months. If you need more detailed views, you can select multiple items at once.

Grouping menu


Fields in the Pivot table can be renamed, so I changed the labels to make the table nicer 🙂

Pivot table grouped by month

Video tutorial:

Add a comment »One comment to this article

  1. Is this solution used for count number of occurrences? Can I do it with macro?


Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress