You can use the following formula to find the most common value in Excel based on criteria:
This particular formula finds the most common value in the range B2:B14 where the corresponding value in the range A2:A14 is equal to the value specified in cell E2.
The following examples show how to use each method in practice.
Example: How to Find Most Common Value with Criteria in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we would like to find the most common value in the Position column only among players who have a value of Spurs in the Team column.
We can specify “Spurs” in cell D2 and then type the following formula into cell E2:
The following screenshot shows how to use this formula in practice:
The formula returns the value Center, which represents the most common position among players on the Spurs team.
We can verify this is correct by manually identifying each player on the Spurs team:
We can calculate the following count of positions for the Spurs:
- Forward: 1
- Center: 3
- Guard: 1
Thus, our formula is correct that Center is the most common position among players on the Spurs team.
The following tutorials explain how to perform other common operations in Excel: