01 / 14Un Proveedor se Cayó por Debajo de la Reclamación

Matrices DinámicasUNICOSSUMAR.SI.CONJUNTO

Error #¡DESBORDAMIENTO! en Excel y rangos derramados

Abrimos una Cuenta Nueva y Dejamos de Reclamar en Silencio a un Proveedor de Nueve Años, Porque la Lista se Derramó una Fila Más que la Columna de al Lado

25 oct 202615 min de lectura

UsaUNICOSORDENARSUMAR.SI.CONJUNTOFILTRAR

Calderhay Wholesale reclama un rápel por volumen a nueve proveedores. Durante tres trimestres lo reclamó a ocho. Nada en la hoja estaba mal, nada salía en rojo, y 24.587,50 € no se pidieron nunca.

La hoja de reclamación son dos columnas. La columna H derrama la lista de proveedores que sale del libro mayor de compras, y la columna I suma al lado las compras netas de cada uno.

La columna H es una fórmula que escribe tantas filas como necesite. La columna I eran ocho fórmulas, escritas una sola vez. En enero se añadió un noveno proveedor, la lista creció una fila y la columna de al lado no.

Rápel del 3T 2026Importe
Ganado sobre las compras67.257,75 €
Reclamado a los proveedores58.724,75 €
Diferencia8.533,00 €
Trimestres afectados3
Aún dentro del plazo8.533,00 €

Alcance. Rangos de desbordamiento, el operador #, #¡DESBORDAMIENTO!, la @ de la intersección implícita y UNICOS, ORDENAR, FILTRAR y SUMAR.SI.CONJUNTO en Microsoft 365 y Excel 2021 y posteriores.

Excel 2019 y anteriores no tienen matrices dinámicas. La sección 11 cuenta qué hacen esas versiones con un archivo escrito así.

En todo el artículo, Lineas es la tabla del libro mayor de compras, la columna H guarda la lista de proveedores y la columna I los totales de al lado.

01Un Proveedor se Cayó por Debajo de la Reclamación

Calderhay compra a una lista corta de proveedores y reclama a cada uno un rápel por volumen al cerrar el trimestre. Los porcentajes están en los contratos de suministro; las compras están en una tabla llamada Lineas.

La hoja de reclamación se montó en 2023 con ocho proveedores. H2 tenía =ORDENAR(UNICOS(Lineas[Proveedor])), que derramaba ocho nombres hasta H9, e I2 tenía =SUMAR.SI.CONJUNTO(Lineas[Neto];Lineas[Proveedor];H2), arrastrada hasta I9.

En enero de 2026 el equipo de compras abrió una cuenta con Calderbrook Organics. La lista la ordenó en tercer lugar, todos los nombres de debajo bajaron una fila, y la lista pasó a terminar en H10.

La columna I seguía acabando en I9. Cada una de sus ocho fórmulas releyó el nombre que había entrado en su fila, así que los ocho totales eran correctos: los ocho primeros de una lista de nueve.

Harlings Dry Goods es el noveno nombre, y ninguna fórmula apunta a él. Llevaba nueve años suministrando a Calderhay, con el mejor porcentaje de la casa, y se le reclamó cada trimestre hasta el trimestre en que se dio de alta a otro.

Nueve Proveedores, Ocho Totales y un Rápel que Nadie Pidió

La reclamación trimestral de rápel de Calderhay Wholesale, en A1:F13. La columna B es la celda de la hoja de reclamación que contiene el nombre de cada proveedor, escrita por una sola fórmula en H2 que se derrama tan abajo como haga falta. La columna D es el rápel que ganaron las compras de cada proveedor según su contrato de suministro; la columna E es lo que realmente se reclamó. La columna de totales que hay al lado de la lista acaba en I9, así que ninguna fórmula apunta al noveno nombre, y las tres filas finales son el trimestre, los dos trimestres anteriores y la parte que sigue dentro del plazo de noventa días.

ABCDEF
1
Supplier
Spill cell
Net purchases
Rebate earned
Rebate claimed
What happened
2
Abbeymoor Produce
H2
412600
12378
12378
Produce and salad, 3.0% on everything over £250,000 a quarter. First alphabetically since the sheet was built, so its total has sat in I2 for three years and has never been wrong.
3
Brindlewood Meats
H3
338900
8472.5
8472.5
Butchery, 2.5% flat. Second on the list before January and second after it, because the new account sorts below it.
4
Calderbrook Organics
H4
96400
2410
2410
The account opened in January 2026 at 2.5%. The smallest supplier on the sheet, claimed correctly every quarter, and the reason everything below it moved down a row.
5
Carrowdale Bakery
H5
196250
4906.25
4906.25
Bakery, 2.5%. Sat in H4 for three years and has been in H5 since January; its total followed it, because the SUMIFS in each row reads whatever name is in column H beside it.
6
Dunmorey Fisheries
H6
271400
8142
8142
Fish and shellfish, 3.0%. Moved down a row in January along with everything under Calderbrook, and claimed in full in every quarter since.
7
Esker Fine Foods
H7
154800
3870
3870
Speciality and deli, 2.5%. The second smallest account and the one most people assumed would be the one to go missing.
8
Fairholm Beverages
H8
389700
11691
11691
Soft drinks and juice, 3.0%. The second largest rebate on the sheet, which is why nobody reading the claim total thought it looked light.
9
Glenrothan Frozen
H9
228500
6855
6855
Frozen goods, 3.0%. The last row the totals column reaches: I9 is the final SUMIFS anybody ever filled down, and it reads H9.
10
Harlings Dry Goods
H10
243800
8533
0
Dry goods and ambient, 3.5% — the best rate Calderhay holds, on an account that has supplied it for nine years. It sorts last, so January pushed it to H10 and no formula has pointed at it since.
11
Q3 2026 claim
9 names, 8 totals
2332350
67257.75
58724.75
The quarter as submitted. The ledger foots, every total agrees with it to the penny, and the claim is short by exactly one supplier's rebate.
12
Q1 and Q2 2026
Same fault, twice
458700
16054.5
0
Harlings supplied £231,800.00 and £226,900.00 in the first two quarters of 2026 and earned £8,113.00 and £7,941.50. Both claim windows have closed.
13
Still claimable
Q3 only
243800
8533
0
The supply agreement allows ninety days from the quarter end. Q3 is inside it; the other £16,054.50 is not, and was written off in October.

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

02Una Lista Derramada Es una Fórmula, No Nueve Celdas

=ORDENAR(UNICOS(Lineas[Proveedor])) vive en una sola celda. Todo lo que hay debajo es resultado: Excel escribe la respuesta en tantas celdas como necesite, y esas celdas no tienen fórmula propia.

Haz clic en H5 y la barra de fórmulas muestra la fórmula en gris, con H2 como única celda editable. Borra H2 y desaparece la lista entera. Ese bloque es el rango de desbordamiento, y su tamaño lo decide el dato.

Una columna de fórmulas arrastradas es lo contrario. Cada celda es su propia fórmula, cada una la puso una persona, y la columna mide exactamente lo que medía el día que alguien la arrastró.

El fallo es tener esas dos cosas una al lado de la otra. Una crece sola y la otra necesita a alguien, así que coinciden solo hasta que cambian los datos.

Escenario: Haz clic en una celda que esté por debajo del inicio de una lista derramada en tu libro. Si la barra de fórmulas sale en gris, estás dentro de un derrame; escribe =FILAS(H2#) en una celda libre y mira cuántas filas escribe hoy.

Pruébalo en la cuadrícula

03H2# Es el Rango que Crece con Ella

Excel tiene una referencia para todo el derrame: la celda superior con una # detrás. H2# son todas las filas que escribe ahora mismo la fórmula de H2: nueve hoy, diez mañana, sin que nadie edite nada.

Así que la columna de totales también debería haber sido una sola fórmula:

=SUMAR.SI.CONJUNTO(Lineas[Neto];Lineas[Proveedor];H2#)

Esa derrama su propia respuesta desde I2, un total por nombre, haya los nombres que haya. El total de la reclamación pasa a ser =SUMA(I2#), que lee lo que la columna haya escrito.

Escrito asíFilas que cubreQué pasa con un noveno proveedor
SUMAR.SI.CONJUNTO arrastrada por I2:I9Ocho, para siempreOcho totales y nueve nombres
SUMAR.SI.CONJUNTO con H2# como criterioLas que escriba H2Nueve totales
=SUMA(I2:I9)Ocho, para siempreSe deja fuera el noveno
=SUMA(I2#)Todo el derrameLo incluye

Escenario: Pon =FILAS(H2#) al lado de =FILAS(I2:I9) en tu propia hoja. Cada vez que esos dos números discrepen, algo del lado derecho se está quedando fuera de un total.

Pruébalo en la cuadrícula

04Qué Significa #¡DESBORDAMIENTO! y Cómo Quitarlo

#¡DESBORDAMIENTO! es el problema contrario, y es el ruidoso. La fórmula quiere escribir en celdas que no están vacías, así que en lugar de pisarlas no escribe nada y devuelve un error.

Pulsa el triángulo de aviso y Excel nombra el motivo. El rango de desbordamiento no está en blanco es el habitual, con Seleccionar celdas obstructivas debajo, que lleva el cursor a lo que estorba: casi siempre un espacio suelto o un encabezado viejo.

Los otros cuatro conviene conocerlos por su nombre, porque cada uno se arregla de otra forma.

Lo que dice el triánguloQué pasa en realidad
El rango de desbordamiento no está en blancoHay un valor, un espacio o una nota en la salida
El rango contiene celdas combinadasUna matriz dinámica no escribe en una celda combinada
El rango de desbordamiento es desconocidoEl tamaño depende de algo volátil, como ALEATORIO.ENTRE
El rango de desbordamiento es demasiado grandeEl resultado se sale de la última fila o columna
Rango de desbordamiento en tablaLa fórmula está dentro de una tabla, que no admite derrames

Escenario: Escribe cualquier cosa en la celda justo debajo de una lista derramada y mira cómo la lista entera se convierte en #¡DESBORDAMIENTO!. Pulsa Ctrl+Z y cuenta las filas libres que tienes de verdad por debajo.

Pruébalo en la cuadrícula

05La @ que Escribe Excel para Evitar el Derrame

A veces Excel impide que una fórmula derrame en lugar de dar #¡DESBORDAMIENTO!, escribiendo una @ delante. =@ORDENAR(UNICOS(Lineas[Proveedor])) devuelve un nombre en vez de una lista, y lo devuelve sin ningún error.

Esa @ es la intersección implícita: coge el único valor que está en la misma fila que la fórmula y tira el resto. Es como leía un rango cualquier versión anterior a 2019 cuando el argumento esperaba un solo valor.

Aparece al abrir en 365 un libro escrito en una versión antigua, al pegar una fórmula dentro de una tabla, y cada vez que alguien copia una fórmula de una hoja hecha en una versión anterior.

La respuesta peligrosa es un número. #¡DESBORDAMIENTO! frena la reclamación; la @ suma una fila, deja el resto de la lista sin pedir, y nadie revisa una celda que tiene un número.

Escenario: Pulsa Ctrl+B, pon Buscar dentro de en Fórmulas y busca @ en tu libro. Borra una y comprueba si esa celda estaba contestando con una lista o con una sola fila de ella.

Pruébalo en la cuadrícula

06Los Derrames No Funcionan Dentro de una Tabla

Una tabla de Excel gestiona sus propias filas: inserta una y todas las columnas calculadas se rellenan solas. Una matriz dinámica también gestiona sus filas, y las dos no pueden mandar sobre las mismas celdas.

Por eso una fórmula que derrama, escrita en una columna de tabla, devuelve #¡DESBORDAMIENTO! con Rango de desbordamiento en tabla como motivo. Excel suele ofrecer la @, que la convierte en una respuesta de una fila y sin error.

La hoja de reclamación no es una tabla, y solo por eso funciona H2. El libro mayor que lee sí lo es, y ese es el orden correcto: tablas para los datos, celdas normales para todo lo que derrame.

Volver atrás. Diseño de tabla → Convertir en rango deja la tabla como celdas corrientes, y las referencias estructuradas que apuntaban a ella pasan a ser rangos normales.

Hazlo con la hoja que contiene el derrame, nunca con la que contiene los datos. Que el libro mayor sea una tabla es lo que permite que Lineas[Proveedor] crezca al añadir una fila.

Escenario: Escribe =UNICOS(A2:A20) en una columna libre de cualquier tabla de tu libro y lee el error. Después escribe la misma fórmula fuera de la tabla y mira cómo se derrama.

Pruébalo en la cuadrícula

07Nunca Leas un Derrame por Número de Fila

Una lista ordenada no tiene direcciones fijas. H4 fue Carrowdale Bakery durante tres años y pasó a ser Calderbrook Organics en enero, sin que nadie cambiara nada en el libro.

Cualquier fórmula que apunte a un derrame con un número de fila es una apuesta a que la ordenación no se moverá. =H4 e =INDICE(H2#;4) son dos formas de hacer esa apuesta, y las dos se pierden en silencio.

Léelo por el nombre:

=BUSCARX("Harlings Dry Goods";H2#;I2#)

Eso sigue al proveedor allá donde lo coloque la ordenación, y devuelve #N/A si el proveedor deja de aparecer en el libro mayor, que es una pregunta que alguien debería recibir.

Escenario: Cuenta las celdas de tu libro que apuntan a un bloque derramado con un número de fila. Sustituye una por un BUSCARX contra la columna de nombres y comprueba que la cifra no se mueve hoy.

Pruébalo en la cuadrícula

08Deja Sitio Debajo de Todo lo que Derrame

Un derrame necesita su sitio antes de necesitarlo. Una lista de nueve filas tiene que poder ser de diez en la misma hoja, y eso significa que la celda bajo H10 tiene que estar vacía el día que ocurra.

Así que debajo de un bloque derramado no va nada. Ni un total, ni una nota, ni una segunda tabla. El total de la reclamación va encima de la lista o en una columna propia, donde el crecimiento no lo alcance.

Un total general debajo de una lista dinámica es el #¡DESBORDAMIENTO! más común de un libro de finanzas, porque debajo de la columna es donde ha ido siempre un total.

Dónde va el totalQué pasa cuando crece la lista
Justo debajo de la listaLa lista entera pasa a #¡DESBORDAMIENTO!
Dos filas por debajoBien hasta que la lista crece dos filas
Encima de la lista, en H1Nunca estorba
Una columna más allá, =SUMA(I2#)Nunca estorba, y lee todo el derrame

Escenario: Mueve un total general de debajo de una lista derramada a la fila de encima y cambia su fórmula para que lea el derrame con #. La cifra no debería moverse; el riesgo sí.

Pruébalo en la cuadrícula

09Tres Comprobaciones que Detectan una Columna Corta

La reclamación salió ocho veces y cuadró ocho veces, porque todas sus cifras eran correctas. Lo que faltaba era una fila, y ningún total puede informar de una fila que nunca se le sumó.

Cuenta las filas de los dos lados. =FILAS(H2#)-CONTAR(I2:I9) da cero mientras coinciden y distinto de cero en cuanto dejan de hacerlo. Ponlo al lado de la reclamación, donde alguien lo vea.

Concilia con el origen. =SUMA(Lineas[Neto])-SUMA(I2#) debería dar cero cada trimestre. Cada euro del libro mayor pertenece a un proveedor y solo a uno, así que son dos caminos al mismo número.

Cuenta los nombres. =CONTARA(UNICOS(Lineas[Proveedor])) lee el libro mayor y no la hoja de reclamación, así que un proveedor que aparece por primera vez lo cambia la misma semana.

Escenario: Pon la conciliación en una celda al lado de tu resumen y dale un formato que ponga en rojo cualquier resultado distinto de cero. Es una fórmula, y es la que habría detectado esto en enero.

Pruébalo en la cuadrícula

10Cuándo un Rango Fijo Es la Respuesta Correcta

Un derrame no siempre es lo que quieres. Un impreso de reclamación con ocho líneas numeradas, un informe pensado para caber en una página, una plantilla en la que alguien escribe: todos necesitan forma fija, y un bloque que crece los rompería.

La pregunta es qué lado decide el tamaño. Si lo deciden los datos, usa un derrame y léelo con #. Si lo decide el diseño, usa celdas fijas y comprueba que los datos siguen cabiendo dentro.

Lo que no puede pasar es tener una de cada. Una lista que crece sola, leída por una columna que no, es el fallo del que trata todo este artículo, y mientras lo comete devuelve un número de aspecto razonable.

Escenario: Elige un resumen de tu libro y decide en voz alta cuál de los dos es. Después haz que sus dos columnas respeten esa decisión y pon =FILAS(H2#) en algún sitio para demostrarlo.

Pruébalo en la cuadrícula

11Excel Antiguo, la Web y los Archivos Compartidos

Los rangos de desbordamiento piden Microsoft 365 o Excel 2021. Abre esta hoja en Excel 2019 y UNICOS, ORDENAR y el operador # son nombres desconocidos, así que la lista es una columna de #¿NOMBRE? y cada total de al lado es cero.

Excel para la web derrama con normalidad y sirve para ver lo que ve un compañero con 365. Google Sheets tiene su propio comportamiento de matrices y su propia lista de funciones, así que pasar por ahí no es una ida y vuelta.

Un libro compartido convierte el crecimiento en cosa de otro. Alguien añade una fila al libro mayor, la lista crece en la pantalla de todos, y quien lleva la hoja de reclamación no es quien la ha dejado corta.

Escenario: Abre el libro en Excel para la web y lee el número de filas del derrame. Si no coincide con el escritorio de un compañero, la diferencia está en su versión y no en los datos.

Pruébalo en la cuadrícula

12Nueve Trampas

  1. Una lista derramada al lado de una columna arrastrada. Un lado crece solo y el otro no, y el hueco entre los dos es silencioso.
  2. =SUMA(I2:I9) sobre un bloque dinámico. El total acierta con las filas que le dieron y no dice absolutamente nada del resto.
  3. Un total general justo debajo de un derrame. La lista no crece una fila: se convierte en #¡DESBORDAMIENTO! y desaparece entera.
  4. Leer una lista ordenada por número de fila. H4 es el proveedor que hoy quede cuarto, y ordenar es justo para lo que está la fórmula.
  5. Tomar la @ por un signo de puntuación. Es una llamada que tira todo menos el valor que está en su propia fila.
  6. Escribir una fórmula que derrama dentro de una tabla. Las tablas y las matrices dinámicas mandan sobre sus filas, así que sobra una de las dos.
  7. Celdas combinadas en cualquier punto de la salida. Un derrame no escribe en ellas de ninguna manera, pase lo que pase en la hoja.
  8. Dar por hecho que #¡DESBORDAMIENTO! es el único fallo. El error es la versión de este fallo que se anuncia sola.
  9. Mandar el libro a un Excel 2019. Ni derrames, ni #, y una columna de #¿NOMBRE? donde estaban los proveedores.

13Cinco Ejercicios

  1. Reprodúcelo. Derrama ocho nombres con =ORDENAR(UNICOS(A2:A40)), arrastra un SUMAR.SI.CONJUNTO ocho filas a su lado y añade un noveno nombre que se ordene por el medio. Lee el total.
  2. Arréglalo con #. Sustituye las ocho fórmulas por un solo SUMAR.SI.CONJUNTO con H2# de criterio, añade otra vez el noveno nombre y mira crecer la columna.
  3. Rómpelo a propósito. Escribe un espacio en la celda de debajo de la lista, lee el motivo del triángulo y usa Seleccionar celdas obstructivas para encontrarlo.
  4. Conoce la @. Escribe =@ORDENAR(UNICOS(A2:A40)) y compara lo que devuelve con la misma fórmula sin la @ delante.
  5. Concílialo. Escribe =SUMA(Lineas[Neto])-SUMA(I2#) al lado de tu resumen y confirma que da cero antes y después de cada cambio anterior.

Qué Llevarse

Todas las cifras de todas aquellas reclamaciones eran correctas. Ocho proveedores, ocho totales, ocho rápeles, y cada uno coincidía con el libro mayor al céntimo.

La hoja tenía nueve proveedores. Una fórmula había crecido para decirlo y la columna de al lado no, y 24.587,50 € de rápel se quedaron sin reclamar en tres trimestres.

Un rango de desbordamiento decide su propio tamaño. A todo lo que lo lea hay que decirle que haga lo mismo, con # y no con un número de fila, y todo lo que esté debajo tiene que moverse.

8.533,00 € de los 24.587,50 € siguen dentro del plazo de noventa días. El resto caducó en silencio, en un libro que no mostró ni un solo error.

Comparte este artículo:
Volver al Blog
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy