You can use the following formula to find the most common value in Excel based on criteria:

=INDEX(B2:B14,MODE(IF(A2:A14=E2,MATCH(B2:B14,B2:B14,0))))

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**:

=INDEX(B2:B14,MODE(IF(A2:A14=E2,MATCH(B2:B14,B2:B14,0))))

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.

**Additional Resources**

The following tutorials explain how to perform other common operations in Excel:

How to Create a Percent Frequency Distribution in Excel

How to Count Frequency of Text in Excel

How to Find the 5 Most Frequent Numbers in Excel