Create Grouped Count Totals in Excel with a PivotTable
Question details
Create grouped count totals from thousands of Excel records without manually extracting subtotal rows.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants a count for each group, such as each place attended, from a large source dataset.
- Observed behavior
- The desired result is one count per group and a grand total, generated without manually filtering subtotal rows.
Make sure the source data has a header for each column and a consistent grouping field, such as Place Attended. Check that the field used for the count is populated for every record you want to count.
Use an Excel PivotTable to count records by group
Generate one count per group and a grand total directly from the source records.
Click any cell in the source data, or select the full range including its column headers.
Select Insert > PivotTable, confirm the table or range, choose where to place the PivotTable, and select OK.
In the PivotTable Fields pane, drag the grouping field, such as Place Attended, to the Rows area.
Drag the same field to the Values area. If it is not summarized by Count, open its Value Field Settings and select Count.
Review the count beside each group and the grand total at the bottom of the PivotTable.

Create grouped counts with a WPS Spreadsheet PivotTable
Open your workbook in WPS Spreadsheet and use a PivotTable to count records by group without extracting subtotal rows.
- 1. Open the workbook: Open WPS Spreadsheet and select File > Open to choose your Excel workbook.
- 2. Create a PivotTable: Click inside the source data, select Insert > PivotTable, confirm the data range and destination, and select OK.
- 3. Set the groups: In the PivotTable field list, drag Place Attended or your grouping field to the Rows area.
- 4. Set the count: Drag the same field to the Values area, then use Value Field Settings to select Count if necessary.

Frequently Asked Questions
Why does my PivotTable show Sum instead of Count?
Excel may default to Sum for numeric fields. In the Values area, open Value Field Settings for the field and select Count.
Will the PivotTable count blank entries?
Count excludes blank cells in the Values field. To count records with a blank grouping field, add a consistently populated field, such as a record ID, to Values and summarize it by Count.
How do I update the counts after changing the source data?
Right-click the PivotTable and select Refresh. If you added rows outside the original source range, use PivotTable Analyze > Change Data Source to include them.
How can I show the grand total if it is missing?
Click the PivotTable, then use Design > Grand Totals and choose an option that turns on grand totals for columns.




