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.

Solution

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.

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

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 now shows how many times are the products in our source table.

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 now looks like this (view by days):

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.

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

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

Video tutorial: