Excel: Find Most Common Value with Criteria

By /

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:

Excel find most common value with criteria

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

x

Related terms:

  1. How do I find the minimum value in excel given multiple criteria?
  2. Common Sense Views of Behavior
  3. What are the 7 Common Types of Regression (And When to Use Each)?
  4. Which is the most common type of conditional distribution in statistics?
  5. Common Fate
  6. Common Source Bias
  7. Law Of Common Fate
  8. The Most Common Adverse Effects of Testosterone Therapy
  9. How do I sum a column if the value in that column is less than a given value?
  10. How to hide rows based on criteria in excel?