À propos de cet exercice
Un jeu de données de 300 lignes sur une feuille, 3 cellules de réponse, environ 10 minutes. Corrigé dans votre navigateur : la valeur et la formule qui la produit, avec des indications sur la correction à apporter.
Comment le résoudre
- J2
=FILTER(A2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3),"No orders") - H4
=ROWS(FILTER(A2:A301,(TRIM(C2:C301)=H2)*(E2:E301>H3))) - H5
=SUM(FILTER(E2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3)))
FILTER keeps the rows of A2:E301 for which the include argument is TRUE. Two conditions are combined by multiplying them: TRUE*TRUE is 1, anything else is 0, so (TRIM(C2:C301)=H2)*(E2:E301>H3) is an AND that works across a whole column at once. TRIM matters because "West " with a trailing space is not equal to "West" — and the first large West order in this log is typed exactly like that — while > keeps the $1,000.00 order out, because it is not over $1,000. The optional third argument replaces the #CALC! error with "No orders" when nothing qualifies, and ROWS and SUM around the same FILTER give the count and the total.
Pitfall 1: text comparisons in Excel ignore case but not spaces; TRIM also collapses double spaces inside a name, while CLEAN removes the non-printing characters that copy-pastes bring along. Pitfall 2: use * for AND and + for OR between array conditions; AND() and OR() collapse the arrays to a single TRUE/FALSE and FILTER then returns everything or nothing. Pitfall 3: ROWS(FILTER(...,"No orders")) returns 1 for an empty result because the fallback text is a 1×1 array — count with a FILTER that has no fallback, or with SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3)), which returns 0. Alternatives: SUMPRODUCT handles the count, and SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3),E2:E301) the total, in Excel 2019; COUNTIFS and SUMIFS are faster on very large tables but cannot TRIM the criteria range, so they miss the 3 rows typed "West " here — clean the column first. Layout: a spilled list grows and shrinks, so never put anything below or to the right of its anchor; SORT(FILTER(...),5,-1) sorts the result by amount, and CHOOSECOLS(FILTER(...),1,5) returns only the columns you need. Version note: FILTER and SORT need Excel 365 or Excel 2021 (CHOOSECOLS: Excel 365); in Excel 2019 the equivalent is an AutoFilter plus SUBTOTAL, or Advanced Filter for a static copy.
Autres méthodes valables
- =SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3)) — the count without FILTER (works in Excel 2019)
- =SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3),E2:E301) — the total without FILTER
- =FILTER(A2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3)) — the same list without the fallback; shows #CALC! when nothing matches
- =SORT(FILTER(A2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3),"No orders"),5,-1) — largest order first (a different order, so not for this check)
Erreurs fréquentes
Comparing the region without TRIM: =FILTER(A2:E301,(C2:C301=H2)*(E2:E301>H3)) skips the 3 matching orders typed "West " — including the very first one, so J2 shows SO-41012 instead of SO-41008 and H4 shows 48 instead of 51.
Comment nous corrigeons
Toute formule qui produit le bon résultat est acceptée, sauf si la tâche nomme une fonction : une autre fonction rapporte alors la moitié des points. Nous comparons les valeurs avec une petite tolérance et ignorons la casse et les espaces dans le texte. Un nombre correct saisi à la main au lieu d’une formule rapporte la moitié des points. Les tâches sur plusieurs cellules rapportent tous les points avec 100 % de cellules justes, la moitié à partir de 50 %. Lorsque c’est pertinent, nous modifions aussi une cellule d’entrée sur une copie de votre feuille et vérifions que votre formule suit.
Réponses calculées selon les règles d’Excel 365 et vérifiées dans notre correcteur le 16 novembre 2026. Auteur : GridBelt team.