📊 Google Sheets Formula Translator

Formula: Display today's top ticket category, with tie-breaking and empty-state handling

The Formula

=LET(
  cutoff, NOW() - 1,
  recent, FILTER(tickets!C2:C, tickets!B2:B >= cutoff, tickets!B2:B <> ""),
  IF(
    COUNTA(recent) = 0,
    "No tickets today.",
    "Top reason today: " & INDEX(
      SORT(
        QUERY(recent, "select Col1, count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc, Col1 asc", 0),
        2, FALSE,
        1, TRUE
      ),
      1, 1
    )
  )
)

(Replace tickets!C2:C and tickets!B2:B with your actual sheet name + ranges.)

How It Works

• LET binds intermediate values (cutoff, recent) so the formula stays readable and doesn't recompute things twice.
• cutoff is NOW() - 1 (one day ago, since 1 = 1 day in Sheets datetime arithmetic).
• FILTER keeps only category values from rows where created_at >= cutoff AND the date isn't blank (the blank check prevents trailing empty rows from being treated as 'before cutoff').
• COUNTA(recent) = 0 checks for the empty-state and returns 'No tickets today.' if nothing matched.
• QUERY(recent, ...) groups categories and counts them. The order by count(Col1) desc, Col1 asc clause does the tie-breaking: highest count wins, ties broken alphabetically.
• INDEX(SORT(...), 1, 1) picks the first row's first column — the winning category. (The SORT step is redundant given QUERY's order-by; included for resilience if QUERY's tie-handling ever changes.)

Edge Cases Handled

1. Empty range (no tickets in last 24h): `COUNTA(recent) = 0` triggers; formula returns 'No tickets today.'

2. All tickets from yesterday or earlier: `FILTER` returns empty array, same as above.

3. Tied categories: `order by count(Col1) desc, Col1 asc` in QUERY breaks ties alphabetically. 'billing' beats 'bug' if both have 3 tickets. (Adjust direction if you want different tiebreak.)

4. Trailing blank rows in source range: `tickets!B2:B <> ""` filter removes them from consideration.

5. Created_at as text instead of datetime: if dates were entered as strings, `>= cutoff` would fail silently. The formula assumes datetime values. (See 'Edge Cases NOT Handled' below.)

Edge Cases NOT Handled

1. Created_at stored as text/string: the comparison `B2:B >= cutoff` requires datetime values. If your sheet has 'April 28, 2026 9:30 AM' as a text string, you'd need `DATEVALUE` wrapping. Easy to add if needed.

2. Timezone issues: `NOW()` returns the spreadsheet's timezone. If your tickets have UTC timestamps and your sheet is in PT, the cutoff is shifted 7-8h.

3. Null categories: `where Col1 is not null` in QUERY filters them. If you want them to count as 'unknown,' wrap with `IFNULL` or change the query.

4. Performance on >100K tickets: QUERY scales fine to ~100K rows. Beyond that, consider a pivot table or moving aggregation upstream.

Cleaner Alternative

If you don't need the tied-category tiebreak in the formula itself, the SORT step is redundant. Shorter version:

=LET(
  recent, FILTER(tickets!C2:C, tickets!B2:B >= NOW()-1, tickets!B2:B <> ""),
  IF(COUNTA(recent)=0, "No tickets today.",
    "Top reason today: " & INDEX(
      QUERY(recent, "select Col1 where Col1 is not null group by Col1 order by count(Col1) desc, Col1 asc limit 1", 0),
      2, 1
    )
  )
)

The limit 1 makes QUERY return just the top row. Saves one SORT call.

Why your original failed: the where B>=date now-1 clause has two issues. First, QUERY's date keyword expects a string literal like '2026-04-27', not an expression. Second, QUERY can't reference a function like now(). The fix is to filter BEFORE the QUERY using FILTER (as above) so QUERY only sees pre-filtered data.

When to NOT Use This Formula

• If you need this on a dashboard with 10+ similar metrics: consider a single QUERY that returns all metrics as a small table, instead of one cell-formula per metric. Faster recalc.
• If your tickets sheet has 500K+ rows and grows daily: Sheets struggles. Move aggregation to BigQuery (via Connected Sheets) or pre-aggregate in App Script that runs every 15 minutes.
• If non-technical users will edit the formula: the LET-bound version is more readable, but a pivot-table is more editable. For 'top category today,' a pivot table on the dashboard tab might serve you better.

Performance Note

Volatile (recalculates on every sheet change) due to NOW(). This is expected — you want 'last 24h' to mean now, not the last time you opened the sheet. If you need to reduce recalc, replace NOW() with a refresh-on-demand cell that has =NOW() and only recalculates when you press F5.

FILTER + QUERY combined: O(n) where n = recent rows. Fast even on 50K-row sheets.

Test Cases

1. 5 tickets today, 3 are 'bug', 2 are 'billing': Returns 'Top reason today: bug' (count=3 wins).

2. No tickets today, but 4 from yesterday: Returns 'No tickets today.'

3. 3 tickets today, 2 'billing', 1 'bug': Returns 'Top reason today: billing'.

4. Tied: 2 'billing', 2 'bug': Returns 'Top reason today: billing' (alphabetically first).

5. Empty data range (sheet has just headers): Returns 'No tickets today.'

Maintenance-Friendly Version

With named ranges + comments for future-you:

=LET(
  // Define ranges once. If you add columns, only update here.
  ticket_categories, tickets!C2:C,
  ticket_dates,      tickets!B2:B,
  // 1 = one day ago in Sheets datetime math
  cutoff,            NOW() - 1,
  recent,            FILTER(ticket_categories, ticket_dates >= cutoff, ticket_dates <> ""),
  empty_state,       "No tickets today.",
  prefix,            "Top reason today: ",
  IF(
    COUNTA(recent) = 0,
    empty_state,
    prefix & INDEX(
      QUERY(recent, "select Col1 where Col1 is not null group by Col1 order by count(Col1) desc, Col1 asc limit 1", 0),
      2, 1
    )
  )
)

(Note: Sheets doesn't support // line comments inside formulas; in real use, put the explanations in a cell next to the formula or in a separate 'README' tab. The variable names alone (ticket_categories, cutoff, empty_state) make the formula self-documenting.)