Sobre este ejercicio
Un conjunto de datos de 300 filas en una hoja, 3 celdas de respuesta, unos 10 minutos. Se corrige en tu navegador: el valor y la fórmula que lo produce, con comentarios que te dicen qué arreglar.
Cómo resolverlo
- 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.
Otras formas válidas
- =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)
Errores frecuentes
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.
Cómo corregimos
Se acepta cualquier fórmula que produzca el resultado correcto, salvo que la tarea nombre una función: entonces otra función obtiene media puntuación. Comparamos los valores con una pequeña tolerancia y, en el texto, ignoramos mayúsculas y espacios. Un número correcto escrito a mano en lugar de una fórmula obtiene media puntuación. Las tareas de varias celdas obtienen la puntuación completa con el 100% de las celdas correctas y media puntuación desde el 50%. Cuando importa, también cambiamos una celda de entrada en una copia de tu hoja y comprobamos que tu fórmula se adapta.
Respuestas calculadas con las reglas de Excel 365 y comprobadas en nuestro corrector el 16 de noviembre de 2026. Autor: GridBelt team.