Pivot Table Issues

Create Grouped Count Totals in Excel with a PivotTable

Olivia MillerOlivia Miller Nov 18, 2026 870 views

Question details

Create grouped count totals from thousands of Excel records without manually extracting subtotal rows.

Create Grouped Count Totals in Excel with a PivotTable
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.
Before you start

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.

Solution 1Recommended

Use an Excel PivotTable to count records by group

Generate one count per group and a grand total directly from the source records.

1
Select the source data

Click any cell in the source data, or select the full range including its column headers.

2
Insert a PivotTable

Select Insert > PivotTable, confirm the table or range, choose where to place the PivotTable, and select OK.

3
Add the grouping field

In the PivotTable Fields pane, drag the grouping field, such as Place Attended, to the Rows area.

4
Count the records

Drag the same field to the Values area. If it is not summarized by Count, open its Value Field Settings and select Count.

5
Check the totals

Review the count beside each group and the grand total at the bottom of the PivotTable.

Use an Excel PivotTable to count records by group
Blank values are not counted: Count excludes blank cells in the Values field. If the grouping field can be blank, use a field populated for every record, such as a record ID, in the Values area instead.
Count groups in WPS Spreadsheet

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. 1. Open the workbook: Open WPS Spreadsheet and select File > Open to choose your Excel workbook.
  2. 2. Create a PivotTable: Click inside the source data, select Insert > PivotTable, confirm the data range and destination, and select OK.
  3. 3. Set the groups: In the PivotTable field list, drag Place Attended or your grouping field to the Rows area.
  4. 4. Set the count: Drag the same field to the Values area, then use Value Field Settings to select Count if necessary.
Summarize thousands of records by groupDisplay group counts and a grand totalOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet menus and tools
microsoft office alternative - wps office

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.