Conditional mode with criteria

To calculate a conditional mode with one or more criteria you can use an array formula based on the IF and MODE functions. In the example shown, the formula in F5 is: {=MODE(IF(group=E5,data))} where "group" is the named range B5:B14, and "data" is the named range C5:C14. Note: this is an array formula and must be entered with control + shift + enter.

April 14, 2025 · 1 min · 68 words · Harry Weber

Table of Contents

Explanation

The MODE function has no built-in way to apply criteria.

Given a range, it will return the most frequently occurring number in that range.

To apply criteria, we use the IF function inside MODE to filter values in a range.

Excel formula: Most frequently occurring number

This array becomes a filter.

For each TRUE, IF returns the corresponding value in the named range “data”.

FALSE values remain unchanged.

Excel formula: List most frequently occurring numbers

For example, =MODE(1,2,4,4,5,5,5,6) returns 5.

Excel formula: Most frequently occurring text

Excel MODE function