MODE.MULT in Google Sheets: Return All Modal Values with a Frequency Audit

• Introduced: first introduction date is not established by the official history reviewed; no desktop release number is inferred.
• Availability: documented in the rolling Google Sheets service; these examples use the web worksheet workflow.
• Checked: October 14, 2026; official MODE.MULT reference. Documentation and independent models, with no native Google Sheets execution or wider client certification.

=MODE.MULT(value1,[value2,...])

Google’s argument table describes a first value or range and repeatable additional inputs. Its worked example supplies one array argument, demonstrating that a single supplied dataset is sufficient.

The output is an array result, rather than a frequency table. Google’s dedicated example displays two results in a column. The expected shapes below follow that documented array presentation; no native Sheets session was run here. Keep an empty output area and inspect the whole result rather than only its first cell.

Enter 4,9,4,9,12,15 in A2:A7.

=MODE.MULT(A2:A7)

Expected modal members: 4 and 9, each once. Both occur twice; 12 and 15 occur once. The expected result needs two cells, not one cell containing the frequency 2.

The values are listed in numeric order here only to identify the members. The dedicated source does not establish a universal ordering rule, so this guide does not certify that every permutation of the input returns modes in the same order.

For numeric 4,4,4,9,9,12, the only most-frequent value is 4. MODE.MULT has one expected modal member even though 9 is also duplicated.

This distinguishes finding the statistical modes from finding all duplicated values. A data-cleaning report looking for every repeated value needs a frequency-based selection rule, not an assumption that MODE.MULT returns every duplicate.

For the first fixture, list candidates 4,9,12,15 in D2:D5 and fill E2 down:

=COUNTIF($A$2:$A$7,D2)

Expected frequencies are 2,2,1,1. Their sum is six, and the maximum is two. Exactly two candidates attain that maximum, matching the two expected modal members.

The helper calculation checks the complete result set independently of any output-order assumption. Preserve the frequencies if a later report needs to explain why a value was returned.

Use a separate H2:H8 range containing 2,5,8,2,5,8,11.

=MODE.MULT(H2:H8)

Expected modal members are 2,5,8. Each occurs twice; 11 occurs once. The result therefore has three values, each of which is a data value rather than a rank or count.

A single visible result is insufficient to validate this formula. Check whether the complete output can expand into its intended cells and whether later formulas consume the full result.

For the first two-mode fixture:

=TRANSPOSE(MODE.MULT(A2:A7))

The expected arrangement is one row with the same two modal members, 4 and 9. TRANSPOSE changes orientation rather than deciding which values are modes. Universal member ordering remains unverified.

For an array that already has only one modal value, transposing its one-cell shape does not create additional modes. Output layout and the number of leaders are separate questions.

Append one additional 9 to the first fixture. Its frequency becomes three while 4 remains at two. The expected modal result now has only 9.

Returning fewer cells is appropriate because the distribution changed. Do not interpret the disappearance of 4 as an error unless the included records or the formula’s reference changed unintentionally.

An actual zero can also be the unique mode. Numeric 0,0,0,4,9 has one expected modal member, 0. A zero result is not automatically a missing-data marker.

For numeric 1,2,3,4, all values occur once. Google’s reference explicitly says MODE.MULT returns an error in that situation. It does not name the error code, so this guide deliberately leaves it unassigned rather than copying #N/A from Excel.

The dedicated body does not fully define ordinary text, numeric text, Booleans, actual empty cells, formula-empty text or source errors. Those cases remain unverified. All worked datasets are numeric and contain clear frequency counts, except the explicitly documented no-repeat error control.

Google’s array example is not evidence for Excel’s surplus-cell rules in a preselected legacy array. This article does not import those rules or prescribe legacy desktop key combinations for the Sheets web workflow.

Use MODE when one unique mode is the intended result, and COUNTIF to make the frequencies visible. An array of modes alone does not describe the mean, spread or complete frequency distribution.

• Google MODE.MULT reference.
• Editorial testing standards.

Examples use fictional inputs, English function names and comma argument separators. Expected outputs are independently modeled from the cited rules, not native Sheets captures. Check the spreadsheet locale when reproducing punctuation-sensitive formulas.