🟩 Sheet Formulas

How to Find the Most Frequent Value in Google Sheets

Use INDEX with MATCH on COUNTIF: =INDEX(A2:A100,MATCH(MAX(COUNTIF(A2:A100,A2:A100)),COUNTIF(A2:A100,A2:A100),0)) returns the most frequent value, text or number.

=INDEX(A2:A100,MATCH(MAX(COUNTIF(A2:A100,A2:A100)),COUNTIF(A2:A100,A2:A100),0))

COUNTIF counts how often each value appears; MATCH finds the position of the biggest count; INDEX returns that value. Works for text and numbers.

How it works

For numbers only, MODE is the quick answer. For text — or a mix — MODE fails, so count every value against the whole column with COUNTIF, find the largest count with MAX, and use MATCH + INDEX to pull back the value at that spot. Google Sheets handles the array inside automatically, with no Ctrl+Shift+Enter.

Variations

Numbers only

=MODE(A2:A100)

Returns the most common number. Errors with #N/A if every value is unique.

All tied top values

=MODE.MULT(A2:A100)

Spills every number that shares the highest frequency when there is a tie.

How many times it appears

=MAX(COUNTIF(A2:A100,A2:A100))

Returns the frequency of the most common value without naming it.

Examples

ScenarioFormula
Most common product name (text)=INDEX(A2:A100,MATCH(MAX(COUNTIF(A2:A100,A2:A100)),COUNTIF(A2:A100,A2:A100),0))
Most common rating (number)=MODE(A2:A100)

FAQ

Why does MODE give an error with text?

MODE only works on numbers. For text, use the INDEX/MATCH/COUNTIF formula, which handles both.

What if two values tie for most frequent?

The INDEX/MATCH version returns the first one found. Use MODE.MULT to list every tied number.

Do I need to press Ctrl+Shift+Enter?

No. Google Sheets evaluates the array inside COUNTIF automatically.

Related formulas

→ See every Google Sheets formula in one cheat sheet