SUBTOTALES y AGREGAR: Totales Que Siguen al Filtro y Sobreviven a los Errores

Esta es una lista de diez notas de gastos con un filtro puesto. El filtro está en Viajes, así que se ven cuatro filas. El total del final de la pantalla dice 3.584,45, que es el mes entero — viajes, software, formación y comidas con clientes, incluidas las seis filas que ahora mismo no puedes ver.

No hay nada roto. A SUMA nadie le ha contado lo del filtro, porque a SUMA no se le puede contar lo del filtro. Lee un rango de celdas, y una celda oculta sigue siendo una celda.

El número que espera quien mira esa pantalla es 2.046,50, y para conseguirlo hace falta otra función — dos, en realidad, y elegir entre ellas se reduce a una pregunta que casi nadie ha tenido que hacerse: ¿qué quieres que se ignore exactamente? Las filas que quitó un filtro. Las filas que ocultó alguien a mano. Las celdas con un error. Los totales que a su vez son totales. Excel tiene una respuesta distinta para cada combinación, y viven en SUBTOTALES y AGREGAR.

Lo que necesitas. SUBTOTALES está en todas las versiones de Excel en uso y también en Google Sheets. AGREGAR llegó con Excel 2010 y está en Excel para la web y en Mac — pero no existe en Google Sheets, cosa que conviene saber antes de montar un libro que va a viajar. Lo demás son menús y teclado: Datos ▸ Subtotal, la fila de totales de una tabla e Ir a Especial.

1) SUMA No Ve el Filtro

Pon =SUMA(E2:E11) debajo de los diez gastos y devuelve 3.584,45. Filtra la columna Categoría por Viajes y sigue devolviendo 3.584,45. Filtra por algo que no tenga ninguna fila y sigue devolviendo 3.584,45.

No es un error ni se arregla con una opción. Filtrar oculta filas; no borra datos. Todas las funciones de Excel salvo SUBTOTALES, AGREGAR y el motor de las tablas dinámicas leen las celdas ocultas igual que las visibles.

Mira la barra de estado con el filtro de Viajes puesto y selecciona la columna E: Excel pone Suma: 2.046,50. Ese es el número que cuadra con lo que hay en pantalla, y está dos centímetros por debajo de una celda que pone 3.584,45. Dos números correctos, una pantalla, y solo uno de los dos se imprime.

El arreglo es una función:

=SUBTOTALES(9; E2:E11)

Con las diez filas a la vista: 3.584,45. Filtrado por Viajes: 2.046,50. Filtrado por Software: 329,00. La fórmula no cambia nunca; el filtro sí.

2) La Anatomía de SUBTOTALES: un Número Que Elige la Función

=SUBTOTALES(núm_función; ref1; [ref2]; …)

El primer argumento no es un rango ni una condición — es un código que elige qué cálculo se hace. Hay once, y cada uno tiene una segunda forma que es el mismo código más 100:

El nueve es SUMA, y por eso =SUBTOTALES(9; …) es el que todo el mundo recuerda a medias. Excel te ofrece la lista en cuanto abres el paréntesis, así que no hace falta memorizar la tabla — pero sí hace falta saber que 9 y 109 no son la misma función, y de eso va la sección siguiente.

3) 9 y 109: la Diferencia Solo Aparece Cuando Alguien Oculta una Fila a Mano

Los dos códigos ignoran las filas que quitó un filtro. Eso es lo que comparten, y en la mayoría de los libros es lo único que llega a pasar.

Se separan con las filas ocultas a mano — clic derecho ▸ Ocultar, un alto de fila arrastrado hasta cero, o un grupo de esquema plegado:

En la lista de gastos, con el filtro en Viajes, las dos devuelven 2.046,50. Ahora haz clic derecho en la fila del C-4104 — los 1.240,00 de Dan Osei — y ocúltala:

• =SUBTOTALES(9; E2:E11) → 2.046,50. Sigue contando la fila que has ocultado.
• =SUBTOTALES(109; E2:E11) → 806,50. Los tres viajes que se ven de verdad.

Cuál quieres es una pregunta sobre quién ocultó qué. Un filtro es una afirmación sobre los datos — enséñame los viajes — así que un total que lo sigue está informando de un subconjunto que alguien ha pedido. Una fila oculta suele ser una afirmación sobre la pantalla — esta fila afea, esta fila es una nota mía — y un total que sigue eso informa del humor que tenía la hoja.

Por defecto, 9. Tira de 109 cuando ocultar filas a mano sea una parte deliberada de cómo se usa la hoja, y deja una nota al lado diciéndolo, porque nadie que lea 109 dentro de seis meses lo va a adivinar.

Una cosa que no hace ninguno de los dos: las columnas ocultas les son invisibles. Oculta la columna E entera y todos los SUBTOTALES que la recorren siguen igual. No existe el equivalente del 109 para columnas, en ninguna versión.

4) La Regla Que Impide Contar Dos Veces

Este comportamiento es el que hace que SUBTOTALES merezca la pena incluso en un libro sin un solo filtro.

SUBTOTALES ignora cualquier otro SUBTOTALES dentro de su rango. SUMA no.

Agrupa los diez gastos por categoría y pon un subtotal debajo de cada grupo — Viajes 2.046,50, Comidas con clientes 258,95, Software 329,00, Formación 950,00 — y el bloque pasa a tener catorce filas: diez gastos y cuatro totales. Pon un total general debajo:

SUMA suma los diez gastos y luego suma los cuatro subtotales, que son otra vez esos diez gastos. Informa de un mes que costó 3.584,45 como un mes que costó 7.168,90, y lo hace en silencio, sin error y sin aviso, en una celda idéntica a cualquier otro total de la hoja.

SUBTOTALES ve las cuatro celdas SUBTOTALES de su rango y pasa por encima. Ese es todo el truco, y por eso el comando Datos ▸ Subtotal escribe fórmulas SUBTOTALES y no SUMA.

Si te llevas una sola costumbre de este artículo: en cualquier bloque que tenga totales de grupo dentro, el total general es un SUBTOTALES.

5) Datos ▸ Subtotal, y la Ordenación Que Hay Que Hacer Antes

Excel te escribe esos totales de grupo solo. Datos ▸ Subtotal abre un cuadro de tres partes: Para cada cambio en (la columna que agrupa), Usar función (Suma, Cuenta, Promedio…) y Agregar subtotal a (las columnas que se totalizan). Marca "Resumen debajo de los datos" o desmárcalo si en tu casa los totales van arriba.

Inserta una fila etiquetada después de cada grupo, un total general al final, todo como SUBTOTALES(9; …), y un esquema en el margen izquierdo con los botones 1, 2 y 3: solo el total general, solo los grupos, todo.

🎯 Escenario: Diez gastos en el orden en que se presentaron, y un responsable que quiere un total por categoría.

La trampa ocupa una línea, y es el motivo por el que casi todo el mundo abandona este comando: ordena primero por la columna que agrupa. "Para cada cambio en Categoría" significa literalmente eso. Con los gastos en orden de presentación — Viajes, Software, Comidas, Viajes, Viajes, Formación… — Excel te obedece al pie de la letra e inserta un subtotal cada vez que el valor de C es distinto del de la fila de arriba. Diez gastos en ese orden dan ocho grupos de cuatro categorías, cada uno un "total" de una o dos filas.

Dos cosas más que conviene saber:

• Quitarlos es Datos ▸ Subtotal ▸ Quitar todos, no seleccionar filas y borrarlas. Borrar a mano deja el esquema puesto.
• Copiar solo lo que se ve. Selecciona el rango, pulsa Alt + ; (Ir a Especial ▸ Solo celdas visibles) y después copia. Sin eso, copiar un rango filtrado o esquematizado se lleva todas las filas ocultas, y te las encuentras justo al pegar.

6) La Columna Que Divide Entre Cero

🎯 Escenario: Los mismos diez gastos y una columna vacía. Rellena F2 con =E2/D2 y cópiala hacia abajo: el coste por día, para poder comparar un viaje de cinco días con uno de un día.

Dos filas no tienen días. C-4102 y C-4108 son licencias de software — renovar no es viajar, así que D es cero — y dividir entre cero da #¡DIV/0!. Dos veces.

Ese único hecho rompe más cosas de este artículo que el filtro:

SUBTOTALES ignora filas ocultas. No ignora errores. Una celda mala en cualquier punto del rango y el total filtrado también es un error, por muy bien que hayas puesto el filtro.

La sección 11 trae la función que lo arregla. Antes, dos cosas que SUBTOTALES hace mejor que nadie.

7) La Fila de Totales de una Tabla Era un SUBTOTALES Desde el Principio

Selecciona los gastos y pulsa Ctrl + T. Después Diseño de tabla ▸ Fila de totales, o Ctrl + Mayús + T. Aparece una fila al final con un desplegable en cada celda — Suma, Promedio, Cuenta, Máx, Mín, Desvest, Var — y elegir Suma escribe:

=SUBTOTALES(109; [Importe])

Fíjate en el 109, elegido por ti, y en la referencia estructurada [Importe] en lugar de E2:E11. Dos consecuencias que interesan:

• Las filas nuevas entran solas. Escribe un gasto en la fila de debajo de la última y la tabla se lo traga; la fila de totales baja y la fórmula no cambia.
• Sigue a los filtros y a las segmentaciones. Todos los totales de esa fila responden a los desplegables de la cabecera, así que una tabla con fila de totales es un resumen de un clic sin escribir una sola fórmula.

Puedes apuntar a la tabla desde cualquier otro sitio del libro igual:

=SUBTOTALES(109; Gastos[Importe])

que totaliza las filas visibles de la tabla Gastos desde una hoja de resumen, y sigue funcionando cuando la tabla crece.

8) Un Número de Orden Que Se Renumera Solo al Filtrar

Escribir 1, 2, 3 por una columna da números que sobreviven al filtro y dejan de tener sentido: filtra por Viajes y te salen las filas 1, 4, 5, 9. Lo que sueles querer es 1, 2, 3, 4 — una cuenta de lo que se ve.

En G2, y copiado hacia abajo:

=SUBTOTALES(103; $B$2:B2)

El rango empieza anclado en $B$2 y termina sin anclar en B2, así que en la fila 5 ya es $B$2:B5. El 103 es CONTARA ignorando filas ocultas, de modo que cada fila cuenta las celdas visibles no vacías que tiene encima, ella incluida. Sin filtro pone del 1 al 10. Con el filtro de Viajes pone 1, 2, 3, 4 en las cuatro filas que quedan en pantalla.

El mismo código, sin expandir, cuenta las filas filtradas:

=SUBTOTALES(103; B2:B11)

Diez sin filtro, 4 en Viajes, 3 en Comidas con clientes, 2 en Software. Júntalo con el total y la línea de resumen se escribe sola: 4 gastos, 2.046,50.

Usa 103 (CONTARA) y no 102 (CONTAR) cuando cuentes texto. Los códigos como C-4101 son texto, y =SUBTOTALES(102; B2:B11) cuenta los números que hay entre ellos, que son cero.

9) La Barra de Estado Dice la Verdad y Luego se le Olvida

Selecciona cualquier rango y la barra de abajo enseña Promedio, Recuento y Suma de las celdas visibles. Con el clic derecho puedes añadir Recuento numérico, Mínimo y Máximo.

Es la comprobación más rápida que hay en Excel: pon el filtro, selecciona la columna, lee el número y compáralo con la celda donde está tu fórmula. Si no coinciden, tu fórmula no se entera del filtro.

Tampoco es un número que puedas usar. No se imprime, no se actualiza dentro de nada y desaparece en cuanto haces clic en otro sitio. Comprueba con ella; informa con una fórmula.

10) AGREGAR: Diecinueve Funciones y Ocho Formas de Ignorar

AGREGAR es SUBTOTALES con lo de ignorar puesto por escrito y la lista de funciones ampliada. Tiene dos formas:

=AGREGAR(núm_función; opciones; ref1; [ref2]; …)     para 1–13
=AGREGAR(núm_función; opciones; matriz; k)           para 14–19

Los números de función son los once de SUBTOTALES, en el mismo orden, más ocho:

El argumento opciones es la parte que no tiene equivalente en ningún otro sitio. Es un número del 0 al 7, y es una lista de lo que hay que saltarse:

Dos de esas ocho hacen casi todo el trabajo en la práctica. La 6 — ignorar errores — y la 7 — ignorar errores y filas ocultas, que es el total a prueba de filtros y de errores que buscaba casi todo el que ha llegado hasta este artículo.

11) El Total Que Sobrevive a un #¡DIV/0!

Volvamos a la columna F, con sus dos celdas #¡DIV/0!.

=AGREGAR(1; 6; F2:F11)

La función 1 es PROMEDIO, la opción 6 es ignorar errores, y la respuesta es 140,41 — el coste medio por día de los ocho gastos que tienen días. Las dos licencias no entran en el promedio como cero ni como ninguna otra cosa; sencillamente no están.

Compara las tres maneras en que esto se suele resolver:

La fila del medio es la peligrosa, porque produce un número creíble. SI.ERROR(…; 0) no se salta los errores — los sustituye, por un valor que después se promedia como cualquier otro. Dos ceros entre diez tiran de la respuesta un 20% hacia abajo, y nada en la pantalla lo dice.

SI.ERROR es la herramienta correcta cuando el cero es de verdad la respuesta — una venta que no existe son cero euros. Es la incorrecta cuando el valor es desconocido o no está definido, y "coste por día de algo que no tiene días" no está definido. Sáltalo, no lo pongas a cero.

Para tener en cuenta el filtro y los errores a la vez, usa la opción 7:

=AGREGAR(9; 7; F2:F11)

Con una advertencia: el "ignorar filas ocultas" de AGREGAR significa todas las filas ocultas, las del filtro y las ocultadas a mano por igual. Se comporta como el 109, no como el 9, y no hay ninguna opción que ignore solo el filtro. Si esa distinción importa en tu hoja, SUBTOTALES(9; …) sigue siendo la única función que la hace.

12) La Segunda Personalidad: K.ESIMO.MAYOR, K.ESIMO.MENOR y una Matriz Sin Ctrl + Mayús + Entrar

Las funciones de la 14 a la 19 llevan un cuarto argumento, k, y abren la parte de AGREGAR que no tiene nada que ver con los filtros.

=AGREGAR(14; 6; F2:F11; 1)     → 248,00   el coste por día más alto
=AGREGAR(14; 6; F2:F11; 2)     → 237,50   el segundo más alto
=AGREGAR(15; 6; F2:F11; 1)     → 64,20    el más bajo
=AGREGAR(12; 5; E2:E11)        → el gasto mediano de las filas visibles

=K.ESIMO.MAYOR(F2:F11; 1) no puede con el primero. Se encuentra un #¡DIV/0! y devuelve #¡DIV/0!. La opción 6 es toda la diferencia.

Y ahora la parte que sí es rara de verdad: en esta segunda forma, AGREGAR acepta una expresión matricial como tercer argumento y la evalúa como matriz sin Ctrl + Mayús + Entrar. En un Excel anterior a las matrices dinámicas eso era casi un superpoder, y hoy todavía ahorra una columna auxiliar:

=AGREGAR(14; 6; (C2:C11="Viajes") * E2:E11; 1)

(C2:C11="Viajes") son diez VERDADERO y FALSO; multiplicados por los importes se convierten en los importes de viajes y un montón de ceros; el K.ESIMO.MAYOR de eso, con k=1, es 1.240,00 — el mayor gasto de viaje del mes, sin columna auxiliar y sin MAX.SI.CONJUNTO.

La imagen simétrica tiene truco. Pide el menor de la misma forma y ganan los ceros: el número más pequeño de esa matriz es 0, y viene de una fila que no es de viajes. Convierte los ceros en errores y deja que la opción 6 los tire:

=AGREGAR(15; 6; 1/(1/((C2:C11="Viajes") * E2:E11)); 1)

El 1/x de dentro convierte cada cero en #¡DIV/0! y deja los importes de viajes como sus inversos; el 1/x de fuera los devuelve a su sitio. La opción 6 tira los errores y K.ESIMO.MENOR devuelve 88,75, el gasto de viaje más pequeño. Parece un truco porque lo es — pero es el idioma estándar, y en un Excel moderno puedes escribir =MIN(FILTRAR(E2:E11; C2:C11="Viajes")) y leerlo en voz alta.

Dos reglas para la 14–19: el argumento k es obligatorio (si lo dejas fuera sale #¡VALOR!), y k tiene que ser como mínimo 1 y como máximo la cantidad de valores (pide el duodécimo mayor de diez y sale #¡NUM!).

13) ¿SUBTOTALES, AGREGAR o SUMAR.SI.CONJUNTO?

Responden a preguntas distintas, y equivocarse es lo que convierte un informe en poco fiable, no solo en incorrecto.

La prueba es una frase: si la respuesta tiene que ser la misma mañana, cuando nadie recuerde qué filtro estaba puesto, no dejes que la decida el filtro.

Una celda de un panel que pone "Total seleccionado: 2.046,50" encima de una lista filtrada es honesta y útil. Ese mismo 2.046,50 en una celda con la etiqueta "Gasto en viajes, agosto" es una trampa, porque el día en que alguien deje el filtro en Software pasa a ser 329,00 en silencio y sigue poniendo Viajes. Esa celda quiere =SUMAR.SI.CONJUNTO(E2:E11; C2:C11; "Viajes"), que dice lo que significa y no la cambia ningún desplegable.

14) Siete Cosas Que Muerden

1. La referencia circular. =SUBTOTALES(9; E2:E12) escrito en E12 se incluye a sí mismo. Excel avisa; el arreglo es dejar el total fuera del rango, o dejar que Datos ▸ Subtotal lo coloque por ti.
2. Agrupar cuenta como ocultar a mano. Pliega un grupo de esquema (Datos ▸ Agrupar) y el 109 descarta esas filas mientras el 9 las mantiene. Igual con un alto de fila arrastrado a cero.
3. 102 contra 103. CONTAR cuenta números, CONTARA cuenta cualquier cosa no vacía. Contar los códigos de gasto visibles — que son texto — con 102 devuelve 0, y 0 es una respuesta equivocada muy creíble.
4. AGREGAR no tiene una opción solo para el filtro. Las opciones 1, 3, 5 y 7 ignoran todas las filas ocultas, se hayan ocultado como se hayan ocultado. Solo SUBTOTALES(9; …) distingue.
5. El anidamiento solo se ignora en las opciones 0–3. =AGREGAR(9; 6; …) sobre un bloque que tenga subtotales de grupo los cuenta dos veces, porque la opción 6 no dice nada del anidamiento. Usa la 2 o la 3 si el bloque lleva totales dentro.
6. Las columnas ocultas no importan nunca. Ninguna de las dos funciones tiene idea de lo que es una columna oculta. Si tu diseño oculta columnas para imprimir, tus totales no cambian.
7. Google Sheets tiene SUBTOTALES y no AGREGAR. SUBTOTALES se comporta igual allí, códigos incluidos. AGREGAR no existe, así que un total que se salte errores hay que escribirlo con SUMAR.SI, FILTRAR o SI.ERROR fila a fila — y un libro que pase por Sheets y vuelva traerá #¿NOMBRE? donde estaban los AGREGAR.

15) Cuánto Valen de Verdad las Dos Funciones

Tres números de esta hojita.

7.168,90 contra 3.584,45. Un bloque de catorce filas con cuatro totales de grupo dentro, sumado con SUMA, informa exactamente del doble de dinero. Nadie se da cuenta de un total duplicado en un mes con un viaje caro; se dan cuenta en el trimestre, cuando la línea de tendencia se pone vertical.

140,41 contra 112,33. Dos celdas #¡DIV/0! en una columna de diez. AGREGAR promedia los ocho valores reales. SI.ERROR(…; 0) promedia diez valores, dos de ellos inventados, y aterriza un 20% por debajo sin ninguna señal visible de que se haya saltado nada.

2.046,50 contra 3.584,45. Viajes es el 57,1% del mes, y un solo gasto — los 1.240,00 de Dan Osei — es el 34,6% él solo. Esas dos frases son todo el sentido de filtrar una lista, y ninguna de las dos se puede decir en voz alta hasta que el total de abajo esté de acuerdo con las filas de la pantalla.

Nada de esto es difícil. Es un argumento de una función, elegido una vez, en la celda que lee todo el mundo y no comprueba nadie.

16) Mini Ejercicios

Usa los diez gastos de arriba.

1. La línea base. Escribe =SUMA(E2:E11) y =SUBTOTALES(9; E2:E11) una al lado de la otra, filtra por Viajes y apunta los dos números. Después filtra por una categoría que no exista y vuelve a apuntarlos.
2. 9 contra 109. Con el filtro de Viajes puesto, oculta a mano la fila del C-4104. Da los dos totales y di cuál pondrías en un resumen de gastos impreso.
3. Rellena la columna F. Escribe =E2/D2 y cópiala hacia abajo. Después saca, en cuatro celdas: el promedio por día ignorando errores, el más alto, el más bajo y cuántas filas dieron un número de verdad.
4. El doble conteo. Ordena por Categoría, ejecuta Datos ▸ Subtotal y luego pon =SUMA y =SUBTOTALES(9; …) sobre el bloque entero, filas de grupo incluidas. Explícale la diferencia a alguien que no haya leído este artículo.
5. El número que se renumera. Pon =SUBTOTALES(103; $B$2:B2) en G2 y cópialo hacia abajo. Filtra por Comidas con clientes y di qué pone G en cada fila visible, y por qué la última es 3.
6. El mayor por categoría, sin columna auxiliar. Usa =AGREGAR(14; 6; (C2:C11="Viajes") * E2:E11; 1) y después escribe la versión que devuelve el segundo mayor gasto de viaje.
7. La trampa del cero. Cambia el 14 por un 15 en la fórmula de arriba y explica la respuesta que sale. Después arréglala.
8. Elige la función. Para cada uno de estos, di si debería ser SUBTOTALES, AGREGAR o SUMAR.SI.CONJUNTO: una celda encima de una lista filtrada con la etiqueta "Seleccionado"; una celda de un informe mensual con la etiqueta "Viajes"; el total de una columna con dos búsquedas #N/D dentro; el recuento de filas que hay ahora en pantalla.

Resumen

SUMA no ve los filtros, y ninguna versión de Excel va a cambiar eso. SUBTOTALES sí: la misma fórmula devuelve 3.584,45 sin filtro y 2.046,50 con Viajes, y la única decisión es 9 o 109 — solo las filas filtradas, o las filtradas más cualquier cosa oculta a mano.

Su otra mitad es la razón de que exista. SUBTOTALES pasa por encima de las demás celdas SUBTOTALES de su rango, así que un total general sobre un bloque de totales de grupo cuenta el dinero una vez. SUMA sobre ese mismo bloque lo cuenta dos veces, en silencio, en una celda igual que todos los demás totales de la hoja.

Lo que SUBTOTALES no sabe hacer es sobrevivir a una celda mala. Un #¡DIV/0! en el rango y la respuesta es #¡DIV/0!, con filtro o sin él. De eso se encarga AGREGAR: diecinueve funciones, un argumento de opciones que deletrea qué hay que saltarse, y la 6 y la 7 haciendo casi todo el trabajo — ignorar errores, ignorar errores y filas ocultas. Además le da a K.ESIMO.MAYOR, K.ESIMO.MENOR, MEDIANA y los percentiles una forma a prueba de errores, y acepta una expresión matricial sin Ctrl + Mayús + Entrar, que es como se saca el mayor gasto de viaje de una columna mezclada sin ninguna auxiliar.

Y luego el criterio, que es la parte que no cubre ningún argumento. Un total que sigue al filtro es honesto en una pantalla que alguien está manejando, y peligroso en una página que alguien va a leer el mes que viene. Si la etiqueta pone Viajes, la fórmula también debería poner Viajes — eso es SUMAR.SI.CONJUNTO, y seguirá teniendo razón cuando hayan cambiado el filtro, hayan reordenado las filas y todos los que montaron la hoja se hayan ido a otra cosa.