You can use the following formula to count the number of occurrences by group in an Excel spreadsheet:
=COUNTIF(group_range, criteria)
The following example shows how to use this formula in practice.
Example: Count by Group in Excel
Suppose we have the following dataset that shows the total points scored by 15 different basketball players:
Now suppose we’d like to count the number of players, grouped by team.
To do so, we can use the =UNIQUE() function to first create a list of the unique teams. We’ll type the following formula into cell F2:
=UNIQUE(B2:B16)
Once we press enter, a list of unique team names will be displayed:
Next, we can use the =COUNTIF() function to find the count of players on each team.
We’ll type in the following formula into cell G2:
=COUNTIF(B2:B16, F2)
We’ll then copy and paste this formula into the remaining cells in column G:
That’s it!
Column F displays each of the unique teams and column G displays the count of players on each team.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate a Five Number Summary in Excel
How to Calculate the Mean and Standard Deviation in Excel
How to Calculate the Interquartile Range (IQR) in Excel