categories.Text & CleanupIntermediate

Build a single modifications line for a café order ticket with TEXTJOIN, then flag the busy ones with COUNTA

TEXTJOIN's ignore_empty argument is what keeps a half-filled order from printing a trail of empty commas on the kitchen screen — and the same blanks it steps over are exactly what COUNTA is built to count.

Task:

A café's point-of-sale system exports each drink order with three customization columns — Milk, Syrup and Topping — but most customers only fill in one or two of them, leaving the rest of that row blank. In column F, join together whichever of those three columns an order actually has into one readable line for the kitchen screen. In column G, mark the order "Yes" if it carries two or more customizations and "No" otherwise. Fill in rows 2 through 7.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

7 rows × 7 columns12 cells you fill in
ABCDEFG
1OrderDrinkMilkSyrupToppingModificationsCustom?
2C-101LatteOat milkVanilla
3C-102Americano
4C-103CappuccinoAlmond milk
5C-104MochaCaramelWhipped cream
6C-105Drip coffee
7C-106LatteSoy milkHazelnutExtra shot
What this exercise teachesMay contain the answer

TEXTJOIN's second argument is the reason to reach for it here instead of &: with ignore_empty set to TRUE, C-101's row joins Oat milk and Vanilla straight into "Oat milk, Vanilla" and steps clean over the blank Topping cell, rather than leaving the stray ", ," that =C2&", "&D2&", "&E2 would print the moment one of three slots is empty — which on this sheet is most of them. C-102's Americano has nothing in any of the three columns, so TEXTJOIN has nothing to join and returns an empty string rather than erroring, which is exactly what a plain order with zero customizations should show. COUNTA asks a different question of the same three cells — not what they say, but how many of them hold anything at all — so C-104's Mocha, with a blank Milk but a filled Syrup and Topping, counts as 2 and clears the "Yes" bar the same way C-106's fully-loaded Latte does, while C-103's single Almond milk stays a "No".