Über diese Übung
Ein Datensatz mit 300 Zeilen auf einem Tabellenblatt, 3 Antwortzellen, etwa 8 Minuten. Bewertet in Ihrem Browser: der Wert und die Formel dahinter, mit Feedback, das die Korrektur benennt.
So lösen Sie die Aufgabe
- G2
=UNIQUE(TRIM(C2:C301)) - J2
=ROWS(UNIQUE(TRIM(C2:C301))) - F2
=COUNTIF($C$2:$C$301,TRIM(C2)&"*")
UNIQUE returns every distinct value of a range once and spills the result into the cells below, so one formula in G2 produces the whole customer list, in the order the names first appear. It ignores letter case — "oakridge marine" and "Oakridge Marine" are one customer — but not spaces, which is why TRIM inside the range is what folds "Ridgeway Analytics " into "Ridgeway Analytics": 58 customers instead of 70. ROWS around the same UNIQUE turns the list into a count, and COUNTIF with the pattern TRIM(C2)&"*" answers the per-order question: how many rows belong to this customer, stray spaces included.
Pitfall 1: Remove Duplicates (Data tab) is destructive — it deletes rows, keeps the first occurrence and has no live link to the source; UNIQUE is a formula, so a new order appears in the list the moment it is typed. Both ignore letter case; neither trims spaces. Pitfall 2: #SPILL! appears when anything sits in the cells the array needs (here G3:G59); clear the path or move the anchor. Pitfall 3: COUNTIF compares like Remove Duplicates — case-insensitive, space-sensitive — and needs a range as its first argument, so it cannot TRIM the column: =COUNTIF($C$2:$C$301,C2) misses the trailing-space copies and is wrong on 78 rows here. The wildcard pattern TRIM(C2)&"*" fixes that, but it would over-count if one customer's name were the start of another's ("Summit Labs" and "Summit Labs Group" — none here); the exact version is =SUMPRODUCT(--(TRIM($C$2:$C$301)=TRIM(C2))), which trims the whole column in memory and is slower on long lists. Alternatives: =SORT(UNIQUE(TRIM(C2:C301))) for an alphabetical list; =UNIQUE(TRIM(C2:C301),,TRUE) lists only the 8 one-off customers; the pre-365 distinct count =SUMPRODUCT(1/COUNTIF(C2:C301,C2:C301)) cannot trim and returns 70. Version note: UNIQUE, SORT and spilling need Excel 365 or Excel 2021; in Excel 2019 put =TRIM(C2) in a helper column, paste it as values and run Data → Remove Duplicates on a copy. Desktop Excel can refer to the whole spill as G2# (=COUNTA(G2#)); this in-browser grid does not support # yet.
Andere Wege, die funktionieren
- =COUNTA(G2:G301) — counts the spilled list for J2 and grows with it
- =SUMPRODUCT(--(TRIM($C$2:$C$301)=TRIM(C2))) — the exact per-order count: compares every trimmed name with this one, no wildcard needed
- =SORT(UNIQUE(TRIM(C2:C301))) — the same names in alphabetical order (a valid list, but this check compares the first-appearance order)
- =SUMPRODUCT(1/COUNTIF(C2:C301,C2:C301)) — pre-365 distinct count; returns 70 because it cannot TRIM
Häufige Fehler
Writing =UNIQUE(C2:C301) without TRIM: the list looks right at a glance but holds 70 entries instead of 58 — from G16 on, every name is one row off because "Ridgeway Analytics " is listed again.
So bewerten wir
Jede Formel, die das richtige Ergebnis liefert, wird akzeptiert, außer die Aufgabe nennt eine Funktion: Dann bringt eine andere Funktion die halbe Punktzahl. Wir vergleichen Werte mit einer kleinen Toleranz und ignorieren bei Text Groß- und Kleinschreibung sowie Leerzeichen. Eine richtige Zahl, die statt einer Formel von Hand eingetippt wurde, bringt die halbe Punktzahl. Aufgaben mit mehreren Zellen bringen die volle Punktzahl, wenn 100% der Zellen stimmen, und die halbe ab 50%. Wo es darauf ankommt, ändern wir außerdem eine Eingabezelle in einer Kopie Ihrer Tabelle und prüfen, ob Ihre Formel mitzieht.
Antworten nach den Regeln von Excel 365 berechnet und am 16. November 2026 in unserem Bewertungssystem geprüft. Verfasst von GridBelt team.