MODE.MULT Function

Returns every most-frequent number, as a list that spills down.

Category

Statistical

Available in

Microsoft 365, Excel 2021+ (spills); Excel 2010+ as an array formula

Returns

A vertical list of numbers

Want to practice MODE.MULT in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

MODE.MULT finds the highest frequency in the data and returns every number that reaches it, in the order they first appear. The results spill down a column.

With a single clear mode it returns just one value, like MODE.SNGL.

Syntax

=MODE.MULT(number1, [number2], …)

Arguments

ArgumentRequired?What it does
number1YesA number, cell or range.
number2, …NoUp to 254 more numbers, cells or ranges.

Examples

Example 1 — Two values tied for most common

3 and 5 each appear twice.

C2fx=MODE.MULT(A2:A7)
ABC
1QuantityModes
233
355
45
58
63
79
=MODE.MULT(A2:A7) Explain

Example 2 — Count the modes

Wrap it in ROWS to see how many values tie.

=ROWS(MODE.MULT(A2:A7)) Explain

Returns 2.

Common mistakes

  • #N/A No number repeats.
  • #SPILL! Cells below the formula aren't empty, so the list has nowhere to go.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.