Table of Contents
Counting by group in Excel is a simple process that involves using the SUMIF function to add up cells in a given range that meet certain criteria. This process can be done easily by entering the SUMIF function into the desired cell and specifying the range of cells to be counted and the criteria for which the cells should be counted. This is a great way to quickly and effectively analyze data and get accurate counts of certain groups within a larger dataset.
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:
Column F displays each of the unique teams and column G displays the count of players on each team.