The MODE.MULT function returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. For horizontal arrays, use TRANSPOSE(MODE.MULT(num_1,num_2,…)).

  • This function was introduced in Excel 2010 and so is not available in earlier versions.
  • This function will return more than one result if there are multiple modes.

Syntax

=MODE.MULT(num_1,[num_2], … )

Arguments

Argument Description
num_1 The first number, cell reference, or range for which you want the mode
[num_2], … Optional. Additional numbers, cell references or ranges for which you want the mode, up to a maximum of 254

Note: Because MODE.MULT returns an array, it must be entered as an array formula. If the formula is not entered as an array formula, a single result will be returned.

Examples

  A B C D E
1 Data   Formula entered in D2:D8 Result Notes
2 2   {=MODE.MULT(A2:A8)} 3 The formula =MODE.MULT(A2:A8)must be entered as an array formula. 
3 3     7  
4 5     #N/A  
5 5     #N/A  
6 7     #N/A  
7 7     #N/A  
8 10     #N/A  

Note: The curly brackets, { and }, seen in the formula entered in D2:D8 are not entered by the user. Excel applies these to show the formula has been input as an array formula.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if a value that is supplied directly to the function, i.e. not part of an array of supplied values, is non-numeric
#N/A Occurs if there are no duplicates, i.e. no mode, within the supplied values

The MODE.SNGL and the MODE.MULT functions both find the statistical mode, the most commonly occurring value, or values, of a supplied set of numbers.

The difference between the functions occurs when the supplied data set has more than one mode. The MODE.SNGL function returns the lowest of these values, whereas the MODE.MULT function returns an array of all of the modes.