Count Rows in Group with Pivot Table in Excel (Step-by-Step Guideline)

We have a sample dataset containing several fruits and their corresponding fruit types. We’ll calculate the number of rows from column B that contain fruits from the Citrus/Melon/Berries group.

Step 1 – Insert Excel Pivot Table to Count Rows in Group

• Convert the dataset into a Defined Table by pressing Ctrl + T.

• Click Summarize with PivotTable.

• The PivotTable from table or range dialog box will open. Select a table or range in Table/Range. Choose the location where you want to place the PivotTable and press OK.

• The Pivot Table will be inserted.

⏩ Note:

You can insert Pivot Table by following the path: Insert > Pivot Table > From Table/Range.

Step 2 – Get the Rows Count in a Group from Pivot Table

To customize the newly created Pivot Table.

• Click on the Pivot Table area to show the PivotTable Fields section.
  

• Drag the Types of Fruit field to the Rows Drag Fruits to the Values area.

• The output shows the count of rows under each type of fruit.

Things to Remember

• It is better to convert the existing data range to an Excel table before inserting the Pivot Table. This way, if you add any new data to the source table, refreshing the Pivot Table will update the Pivot Table data too.
• You can count unique values using Pivot Tables (by adding a Helper column in the source dataset).

Download Practice Workbook

<< Go Back to Pivot Table Calculations | Pivot Table in Excel | Learn Excel