Statistical
Find the most frequently occurring text value in a column
Pair a COUNTIF frequency helper column with XLOOKUP(MAX(...)) to return the modal text category—where numeric MODE fails.
Why MODE.SNGL returns #N/A on text categories
Built-in MODE and MODE.SNGL evaluate numbers only, ignoring strings completely. To identify the top ticket reason ("Billing"), compute each row's frequency in a visible helper column with =SUMPRODUCT(--($A$2:$A$6=A2)).
Then XLOOKUP(MAX(B2:B6), B2:B6, A2:A6) locates the highest count (3) and returns the corresponding category name ("Billing").
| Reason (A) | Freq Helper (B) | Top Category? |
|---|---|---|
| Billing | 3 | Winner (MAX = 3) |
| Login | 1 | |
| Billing | 3 | |
| Export | 1 | |
| Billing | 3 |
Modal text value and peak frequency formulas
Keeping the Freq helper column visible also lets you rank 2nd- and 3rd-place categories or chart category shares immediately.
=XLOOKUP(MAX(B2:B6), B2:B6, A2:A6)Peak occurrence count
MAX(B2:B6) reports how many rows belong to that winning category (3 of 5 = 60%).
=MAX(B2:B6)Inputs and interpretation
Use populated text categories and exclude blanks/errors before applying this example. The equality helper treats * and ? literally and compares case-insensitively. If categories tie, XLOOKUP returns the first tied row; define a different tie policy explicitly if required. Deduplicate category rows before making a category-share chart so repeated helper rows are not double-counted.
Try the example
Use the original values or change them to test your understanding.