When should I use MODE.MULT instead of MODE.SNGL?

Use MODE.MULT when you want to identify all most-common values, especially if you suspect a tie. Use MODE.SNGL when you need exactly one answer and don't care about ties—it returns the smallest mode if multiple values tie.

Does MODE.MULT ignore empty cells or text?

MODE.MULT ignores empty cells and logical values (TRUE/FALSE), but it will error if the range contains text. Ensure your range includes only numeric values or use filtering to exclude text.

Can I use MODE.MULT with multiple separate ranges?

Yes. The syntax =MODE.MULT(range1, range2, range3) combines all ranges as if they were one continuous array, then finds the mode(s) across the combined dataset.

How does MODE.MULT handle decimal scores or percentages?

MODE.MULT treats decimals and percentages the same as integers—it looks for exact numeric matches. A score of 85.5 and 85.0 are treated as different values. If you need to group similar decimals, round them first or use a different approach.

MODE.MULT function

MODE.MULT returns the most frequently occurring value(s) in a dataset—multiple modes if they tie for highest frequency.

=MODE.MULT(number1, ...)

Generate a MODE.MULT formula

Describe what you need. The generator will reach for MODE.MULT where MODE.MULT is the right tool, and tell you when it is not.

Arguments

How MODE.MULT reads its arguments

Argument	Required	Description
number1	Required	Required. A range or array of numeric values to search for the most frequent value(s). Ignores logical values and text.
...	Repeating	Optional. Additional ranges or arrays of numeric values to include in the mode analysis. All arguments are combined before calculating.

Returns

An array of numeric value(s) appearing most frequently; returns a single value if only one mode exists.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the most common quiz score for one student

Student	Subject	Assignment	Score	Max Score
Alex	Math	Quiz 1	85	100
Alex	Math	Quiz 2	85	100
Alex	Math	Quiz 3	90	100

=MODE.MULT(D2:D4)

Result: 85

Alex's scores are 85, 85, and 90. Since 85 appears twice and 90 appears once, MODE.MULT returns 85 as the sole mode. This tells you the most common score Alex achieved across her quizzes.

2. Find all scores that tie for most frequent across the class

Student	Subject	Assignment	Score	Max Score
Alex	Math	Quiz 1	85	100
Alex	Math	Quiz 2	85	100
Alex	Math	Quiz 3	90	100
Blake	Math	Quiz 1	78	100
Blake	Math	Quiz 2	78	100
Blake	Math	Quiz 3	82	100
Casey	Math	Quiz 1	92	100
Casey	Math	Quiz 2	85	100
Casey	Math	Quiz 3	85	100
Dana	Math	Quiz 1	88	100
Dana	Math	Quiz 2	88	100
Dana	Math	Quiz 3	88	100

=MODE.MULT(D2:D13)

Result: 8588

Across all 12 quiz attempts, 85 appears four times and 88 appears four times—a tie for highest frequency. MODE.MULT returns both as an array: {85, 88}. This reveals that no single score dominated; the class was split between two common performance levels.

3. Identify the most common score when one value repeats more often

Student	Subject	Assignment	Score	Max Score
Alex	Math	Quiz 1	88	100
Alex	Math	Quiz 2	88	100
Alex	Math	Quiz 3	85	100
Blake	Math	Quiz 1	88	100
Blake	Math	Quiz 2	92	100
Blake	Math	Quiz 3	82	100
Casey	Math	Quiz 1	92	100
Casey	Math	Quiz 2	88	100
Casey	Math	Quiz 3	88	100
Dana	Math	Quiz 1	78	100

=MODE.MULT(D2:D11)

Result: 88

The score 88 appears five times, more than any other value (92 appears twice, 85 once, 82 once, 78 once). MODE.MULT returns 88 as the single mode, showing this was the most common quiz score in this sample.

Common errors

Which MODE.MULT error are you seeing?

Error	Why it happens	How to fix it
#NUM!	All values in the range appear with equal frequency, leaving no clear mode. For example, =MODE.MULT(D2:D5) where scores are [75, 82, 91, 88]—each appears once.	Ensure your range includes values with unequal frequencies. If you expect ties, use IFERROR to handle this case: =IFERROR(MODE.MULT(D:D), "No unique mode").
#VALUE!	The range contains non-numeric data such as text headers, student names, or letter grades. MODE.MULT cannot calculate frequency for non-numeric values.	Exclude text columns from your selection. Use =MODE.MULT(D2:D100) rather than =MODE.MULT(A2:E100). Alternatively, filter numeric values with =MODE.MULT(FILTER(D:D, ISNUMBER(D:D))).
#REF!	The range reference is invalid—for instance, a column has been deleted, rows have been removed, or the reference points to a closed workbook.	Verify the range still exists by checking the sheet structure. Rebuild the formula with the correct range, such as =MODE.MULT(D2:D100), and confirm both row numbers are valid.

Tips and when to use something else

MODE.MULT returns an array when multiple values tie for highest frequency. To extract just one, wrap it in INDEX: =INDEX(MODE.MULT(D:D), 1).
For a single mode even if ties exist, use MODE.SNGL instead—it returns the smallest value when multiple modes are tied.
When analyzing student grades, MODE.MULT reveals the most common performance level, helping teachers identify whether an assignment's difficulty aligns with the class.
Combine MODE.MULT with COUNTIF to verify the mode's frequency: =COUNTIF(D:D, INDEX(MODE.MULT(D:D), 1)) counts how many times the first mode appears.

Related functions

MODE.SNGLReturns the most commonly appearing numeric value in a dataset, ideal for identifying the most frequent priority level, rating, or resolution time.MEDIANMEDIAN returns the middle value in a sorted list of numbers, or the average of the two middle values if the count is even.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.COUNTIFCounts cells in a range that meet a specific criterion, returning a number — use it to tally matching rows like employee counts by department.

Frequently asked questions

When should I use MODE.MULT instead of MODE.SNGL?: Use MODE.MULT when you want to identify all most-common values, especially if you suspect a tie. Use MODE.SNGL when you need exactly one answer and don't care about ties—it returns the smallest mode if multiple values tie.
Does MODE.MULT ignore empty cells or text?: MODE.MULT ignores empty cells and logical values (TRUE/FALSE), but it will error if the range contains text. Ensure your range includes only numeric values or use filtering to exclude text.
Can I use MODE.MULT with multiple separate ranges?: Yes. The syntax =MODE.MULT(range1, range2, range3) combines all ranges as if they were one continuous array, then finds the mode(s) across the combined dataset.
How does MODE.MULT handle decimal scores or percentages?: MODE.MULT treats decimals and percentages the same as integers—it looks for exact numeric matches. A score of 85.5 and 85.0 are treated as different values. If you need to group similar decimals, round them first or use a different approach.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17