MODE.MULT
Returns a vertical array of the most frequently occurring values in a dataset. Useful when several values tie for most frequent.
MODE.MULT(number1, [number2], ...)Arguments
number1First value or range.number2Optional. Up to 254 additional numbers, references, or arrays.(optional)
=MODE.MULT(1,2,2,3,3,4)Both 2 and 3 occur twice — both returned
- •Available since Excel 2010
- •Returns a dynamic array (Excel 365 / 2021) or must be entered as CSE in older versions
- •Returns #N/A if no value repeats
- •Forgetting that MODE.MULT returns an array, so in older Excel versions it needs to be entered as a CSE (Ctrl+Shift+Enter) array formula, or only the first result will display
- •Using MODE.MULT on a dataset with no repeated values, which returns a #N/A error just like MODE.SNGL
- •Expecting a fixed number of results - MODE.MULT returns as many tied values as actually exist in the data, so the array size varies depending on the dataset
MODE.SNGLReturns only the single most frequent value, a simpler alternative when you don't need every tied value returned.COUNTIFCan independently verify how many times each value returned by MODE.MULT actually occurs in the source data.UNIQUEExtracts distinct values from a range, a different kind of deduplication than MODE.MULT's focus specifically on the most frequent values.Why do I only see one value from MODE.MULT instead of an array?
In older Excel versions, MODE.MULT needs to be entered as an array formula with Ctrl+Shift+Enter, or only the first result displays. In Excel 365, it spills automatically into adjacent cells.
Why does MODE.MULT return #N/A?
No value in the dataset repeats - like MODE.SNGL, MODE.MULT requires at least one value to appear more than once.
How many values does MODE.MULT return?
As many as are actually tied for the highest frequency in your data - this could be two, three, or more values depending on the dataset, not a fixed number.
Need to translate a formula using MODE.MULT?
Use our translator to convert your complete formula