Dynamic Arrays
Conditional Mode: Most Frequent Value with Criteria (MODE.SNGL & FILTER)
Find the most common price point, defect code, or ticket response time within a specific department or region using IFERROR(MODE.SNGL(FILTER(values, criteria)), "No Repeating Mode").
Build the missing "MODEIFS" function with MODE.SNGL + FILTER!
Spreadsheets provide SUMIFS, COUNTIFS, AVERAGEIFS, MINIFS, and MAXIFS—yet there is no built-in MODEIFS function! When a merchandising or QA analyst wants the single most frequently ordered pack size or defect code inside just the "Enterprise" tier, passing the full column to MODE.SNGL mixes every tier together!
Nesting FILTER inside MODE.SNGL solves conditional mode cleanly in one step: =MODE.SNGL(FILTER(B2:B8, A2:A8 = D2))! Remember one vital statistical edge case: if a filtered group has zero matching rows OR every value in that group appears only once (no repeat!), MODE.SNGL returns #N/A—so wrapping with IFERROR(..., "No Repeating Mode") keeps dashboards clean!
| Segment (A) | Seat_Pack_Size (B) | Filtered Values for Segment | Conditional MODE.SNGL |
|---|---|---|---|
| Enterprise | 50, 100, 50, 250 | {50; 100; 50; 250} | 50 (Appears twice) |
| MidMarket | 25, 25, 10 | {25; 25; 10} | 25 (Appears twice) |
| Startup (All Unique) | 5, 10 (if changed) | {5; 10} (No repeats!) | "No Repeating Mode" (IFERROR) |
Conditional Numeric Mode by Category (with IFERROR Guard)
Filters B2:B8 where segment A2:A8 equals D2, computes MODE.SNGL, and catches #N/A or #CALC! when no value repeats or no row matches.
=IFERROR(MODE.SNGL(FILTER(B2:B8, A2:A8 = D2)), "No Repeating Mode")Count How Many Times That Winning Mode Appears in the Category
Uses COUNTIFS to report the frequency of the conditional mode inside segment D2.
=IFERROR(COUNTIFS(A2:A8, D2, B2:B8, MODE.SNGL(FILTER(B2:B8, A2:A8 = D2))), 0)Try the example
Use the original values or change them to test your understanding.