Count Data By Group In Excel

Counting cells in Excel is easy, and most users know how to do it. But how about counting data by 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 state 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.

  1. Click any cell inside the table.
  2. Navigate to Insert >> Tables and click PivotTable.
  3. A new window, called Create PivotTable window appears. Click OK.
  4. Inside PivotTable Fields, click State and Name. The state should be above the Name.
  5. Drag the state from 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 states are counted.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.