Statistical
Most frequent text value with criteria: top ticket reason by region
Find the #1 recurring text label (defect code, ticket category, or top SKU) within a chosen department or region using COUNTIFS + masked XLOOKUP.
Combine pair-level COUNTIFS with boolean-masked MAX + XLOOKUP
The helper in C counts occurrences of each region/reason pair. Multiplying by the selected-region mask excludes the other regions before MAX finds the highest frequency.
XLOOKUP returns the first row with that frequency. If several reasons tie, the earliest one in row order wins; the formula does not return all tied modes.
Use nonblank canonical region and reason labels. The SUMPRODUCT helper uses equality rather than wildcard criteria, so an asterisk in a label is treated literally. Extend all ranges together when adding tickets.
| Region (A) | IssueType (B) | PairFreq (C) | Masked Score for "North" |
|---|---|---|---|
| North | Billing | 2 | 2 (Winner in North -> "Billing"!) |
| North | Login | 1 | 1 |
| North | Billing | 2 | 2 |
| South | Export | 3 | 0 (Zeroed out when querying North!) |
| South | Export | 3 | 0 |
| South | Export | 3 | 0 |
Conditional #1 text mode and peak-count formulas
Notice that South has 3 "Export" tickets globally, yet querying E2 = "North" cleanly returns "Billing" (2 tickets)!
=LET(sc, (A2:A7 = E2) * C2:C7, IF(MAX(sc) = 0, "No Records", XLOOKUP(MAX(sc), sc, B2:B7)))Occurrence count of that #1 text issue within the chosen group
Returns MAX((A2:A7 = E2) * C2:C7).
=MAX((A2:A7 = E2) * C2:C7)Count each literal region/reason pair
Enter in C2 and fill down through C7. Blank regions or reasons receive a zero flag.
=IF(OR(A2="",B2=""),0,SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)))Try the example
Use the original values or change them to test your understanding.