Counting cells in Excel is easy, and most users know how to do it. But how about counting data by a group? It’s also not very complicated, and you can do it just in a few clicks.
This is an example we are going to use in this lesson.

There is a list of 15 people. We want to group these people by states and count the number of particular states.
Count grouped data
The best way to count grouped data is a Pivot table. Here’s how to do it.
- Click any cell inside the table.
- Navigate to Insert >> Tables and click PivotTable.
- A new window, called Create PivotTable window appears. Click OK.
- Inside PivotTable Fields, click State and Name. The state should be above Name.
- Drag the state form the top to Values. Make sure that it’s “Count of State”, and not, for example, “Sum of State”

- Now, each person is grouped by state, and all stated are counted.