Subtotales en Excel: qué son y cómo añadirlos a una tabla

Imagina una hoja con las ventas del mes: fecha, comercial, región e importe. Cuando filtras por una región para ver solo lo que vendió el equipo Norte, =SUMA(D2:D100) sigue dando el total de TODAS las filas, incluidas las que el filtro ha ocultado. La función SUBTOTALES resuelve eso: calcula el total solo con las filas visibles y, además, puede contar, promediar, sacar el máximo o el mínimo sin que tengas que cambiar la fórmula. Al terminar el tutorial sabrás escribir =SUBTOTALES(...) a mano, leer los dos rangos de códigos (1-11 y 101-111), usar el botón Subtotales de la cinta y combinarla con listas desplegables para que el usuario elija la operación. La función existe en todas las versiones de Excel desde 2003 y también en Excel para la web, Mac y móviles.

Qué es SUBTOTALES y por qué no vale cualquier SUMA

SUBTOTALES (en inglés SUBTOTAL) es una función que aplica una operación —suma, promedio, cuenta, máximo, mínimo, producto, desviación estándar o varianza— pero solo sobre las filas visibles de un rango. Si la hoja está filtrada, ignora las filas que el filtro ha ocultado. Si además ocultas filas a mano, depende del código que le pases: los códigos del 1 al 11 incluyen las filas que ocultaste con clic derecho, y los códigos del 101 al 111 las ignoran también.

Para entender por qué importa, volvamos a la hoja de ventas: tienes 14 ventas repartidas entre Norte, Sur y Este, con un total de 6.825,40 escrito con =SUMA(D2:D15). Aplicas un filtro para ver solo Norte y la columna D sigue mostrando la misma cifra, 6.825,40, aunque ahora solo ves 4 filas. La causa es que =SUMA() no sabe nada del filtro: suma todo el rango, esté la fila visible o no. Si cambias esa fórmula por =SUBTOTALES(9;D2:D15), al filtrar la cifra baja a lo que realmente suman las filas visibles, y al quitar el filtro vuelve a 6.825,40. Esa es la diferencia práctica: SUBTOTALES respeta lo que tú o el filtro habéis decidido ocultar, y por eso sirve para totales que «siguen» al filtro en lugar de quedarse fijos.

La sintaxis de SUBTOTALES: el número de función y los rangos

La forma de la función es =SUBTOTALES(núm_función; ref1; [ref2]; …). El primer argumento, núm_función, es un número del 1 al 11 o del 101 al 111 que indica qué operación se hace. A continuación van los rangos sobre los que se calcula, de 1 a 254 referencias; puedes pasarle uno solo, varios separados por punto y coma, o un rango con huecos; si los rangos están en la misma columna, lo normal es uno solo. La función devuelve el resultado de aplicar la operación pedida a las celdas visibles de esos rangos.

Una peculiaridad útil: cuando una celda del rango contiene a su vez otra fórmula =SUBTOTALES(...), SUBTOTALES la ignora para no contar dos veces el mismo dato. Esto pasa sobre todo con los totales automáticos que crea el botón Subtotales de la cinta: cada grupo tiene su SUBTOTALES y el gran total, también hecho con SUBTOTALES, no duplica las cifras.

El primer argumento: el código que decide el cálculo

Estos son los once cálculos que puedes pedir, con sus dos códigos:

Por qué hay dos series de códigos (1-11 y 101-111)

La diferencia entre las dos series es cómo tratan las filas que tú ocultas a mano con clic derecho > Ocultar. La serie 1-11 las incluye en el cálculo; la serie 101-111 las ignora. Las filas que oculta un filtro (o el botón Subtotales) se ignoran SIEMPRE, con ambas series. Esa separación existe porque hay informes donde quieres que «ocultar» signifique esconder de la vista pero no del cálculo (por ejemplo, para que un jefe no vea un detalle pero el total siga sumando esos datos), y otros donde «ocultar» significa excluir del todo.

Dónde está SUBTOTALES en la cinta de opciones

Además de escribir la fórmula a mano, Excel tiene un botón que crea filas de subtotal automáticamente y las marca con los símbolos 1 2 3 del esquema a la izquierda de la hoja.

Al pulsar Subtotales se abre un cuadro con tres listas: «Para cada cambio en» (la columna por la que se agrupa, por ejemplo región), «Usar función» (SUMA, CONTAR, PROMEDIO…) y «Agregar subtotal a» (las columnas que se suman). Al aceptar, Excel inserta una fila debajo de cada cambio de grupo con =SUBTOTALES(9;…) y, debajo del todo, un gran total también con SUBTOTALES. A la izquierda aparecen los símbolos 1 2 3 del esquema: el 1 solo enseña el gran total, el 2 enseña los grupos y el 3 enseña todos los detalles. Es la forma más rápida de probar la función sin teclear nada.

Tu primer SUBTOTALES sobre una hoja de ventas

Vamos con el libro que crece: la hoja «Ventas» de una tienda, con 14 filas, 4 columnas y un total al final.

Pasos:

1. Selecciona la celda D16 y escribe =SUBTOTALES(9;D2:D15).
2. Pulsa Entrar. La celda muestra 6.825,40, la suma de las 14 filas.
3. Compara con =SUMA(D2:D15) en la celda D17: también da 6.825,40.

Filtrar y ver cómo cambia el total

Ahora aplicamos un filtro por región para ver el comportamiento de cada fórmula.

Pasos:

1. Selecciona A1 y ve a Datos > Ordenar y filtrar > Filtro. Aparecen flechas en cada encabezado.
2. Pulsa la flecha de la columna C (Región), desmarca «Seleccionar todo», marca solo Norte y pulsa Aceptar.
3. Mira las celdas D16 y D17.

La celda D16 con SUBTOTALES baja a 2.289,90 (480 + 295 + 622,30 + 354,40 + 538,20). La celda D17 con SUMA se queda en 6.825,40, como si nada hubiera pasado. Quita el filtro y las dos vuelven a coincidir en 6.825,40.

Pasar del código 9 al 109 para excluir lo oculto a mano

Ahora vamos a ocultar una fila a mano y vemos qué pasa con cada código.

Pasos:

1. Quita el filtro de la columna C para ver todas las filas otra vez.
2. Clic derecho sobre la fila 5 (Marta, Este, 740,20) y elige Ocultar.
3. Mira D16 (código 9) y anota la cifra.
4. Cambia la fórmula de D16 a =SUBTOTALES(109;D2:D15) y vuelve a mirar.

Con la fila 5 oculta, D16 con código 9 muestra 6.084,90 (sigue sumando la fila oculta, 6.825,40 − 740,20). Con código 109 muestra 6.084,90 también si solo ocultas esa fila, porque el filtro ya no está activo. La diferencia entre 9 y 109 se ve cuando combinas filtro y ocultas a mano: el código 9 suma las ocultas a mano, el 109 las ignora. En este caso, como no hay filtro, los dos códigos dan lo mismo porque la fila está oculta a mano con código 9 también la incluye. Repite el experimento aplicando primero el filtro por Norte y ocultando después la fila 4 a mano: con código 9 verás que la fila oculta a mano se sigue contando; con código 109 no.

Combinar SUBTOTALES con SI y una lista desplegable

Una lista desplegable para elegir la operación y una fórmula anidada que cambia el código de SUBTOTALES en función de lo elegido.

Pasos previos:

1. En la celda F1 escribe Operación.
2. En F2 escribe la lista: Suma, Promedio, Contar (uno por línea).
3. Selecciona G1 y abre Datos > Herramientas de datos > Validación de datos.

1. Pulsa Aceptar. La celda G1 ahora tiene una lista desplegable con Suma, Promedio y Contar.
2. Selecciona la celda G2 y escribe la fórmula:

=SI($G$1="Suma";SUBTOTALES(9;D2:D15);SI($G$1="Promedio";SUBTOTALES(1;D2:D15);SUBTOTALES(2;D2:D15)))

1. Cambia el valor de G1 con la lista desplegable y observa cómo cambia G2.

La fórmula con SI anidados que cambia de operación

La fórmula =SI($G$1="Suma";SUBTOTALES(9;D2:D15);SI($G$1="Promedio";SUBTOTALES(1;D2:D15);SUBTOTALES(2;D2:D15))) se lee de fuera hacia dentro: el primer SI mira si G1 vale "Suma". Si es así, devuelve la suma de las filas visibles (código 9). Si no, pasa al segundo SI, que mira si G1 vale "Promedio". Si lo es, devuelve el promedio (código 1). Si tampoco, devuelve el conteo de números (código 2), que es la opción "Contar".

La referencia a G1 lleva dólares ($G$1) porque la fórmula podría copiarse a otras celdas; la fila 1 debe quedarse fija. La referencia a D2:D15 no lleva dólares: en este caso se queda aquí, pero si la arrastras hacia abajo para tener un resumen por bloques, se irá adaptando. El truco está en que cada SUBTOTALES(...) se evalúa solo si el SI correspondiente lo elige; los otros dos no se calculan, así que no hay conflicto entre códigos.

Por qué SUBTOTALES a veces no hace lo que esperas

Tres tropiezos reales con esta misma hoja y cómo arreglarlos.

El total no cambia al filtrar

Síntoma: filtras por una región y la celda del total se queda igual que con todas las filas. Causa: la fórmula es =SUMA(D2:D15) o un SUBTOTALES con un código que no es de los que suman (por ejemplo, 1 Promedio, 4 MAX). Arreglo: sustituye =SUMA() por =SUBTOTALES(9;…) si quieres suma, o por el código que corresponda a la operación. Reaplica el filtro y comprueba que la cifra cambia.

SUBTOTALES anidados que se duplican

Síntoma: tras usar el botón Subtotales de la cinta, modificas una de las filas de subtotal a mano o la copias a otra celda y el gran total se dispara. Causa: has puesto dos SUBTOTALES sobre el mismo rango, uno dentro de otro. SUBTOTALES solo ignora los que están DENTRO de su rango; los que están AFUERA no. Arreglo: deja un único SUBTOTALES por rango; si el de abajo es el «gran total» que añade el botón, no lo dupliques en una celda suelta.

El resultado en una fila horizontal no cambia al ocultar columnas

Síntoma: tienes datos en horizontal (por ejemplo, B2:K2) y al ocultar una columna con clic derecho, SUBTOTALES no cambia. Causa: SUBTOTALES está pensado para columnas verticales: las series 101-111 ignoran filas ocultas, no columnas ocultas. Arreglo: usa código 9 a sabiendas de que ocultará menos, o transpón los datos con Inicio > Portapapeles > Pegar > Transponer para que las series pasen a estar en filas y los códigos 101-111 funcionen como esperas.

Lo que SUBTOTALES no puede hacer y qué usar en su lugar

SUBTOTALES tiene tres límites que conviene conocer. Primero, no ignora los errores: si una celda del rango tiene #N/A o #¡DIV/0!, el resultado de SUBTOTALES propaga el error y deja de servir. La función que sí ignora errores y filas ocultas es =AGREGAR(…), en inglés AGGREGATE, disponible desde Excel 2010, y que admite un segundo argumento para decidir qué se descarta (errores, ocultas, ambas, nada). Segundo, no sirve bien en datos en fila: solo la serie 9 incluye lo oculto a mano en horizontal, y no hay forma limpia de ocultar columnas con SUBTOTALES. Tercero, en hojas con cientos de miles de filas y muchos filtros, puede ir más lento que una tabla dinámica; cuando el informe crezca, una tabla dinámica con segmentación de datos cubre lo mismo y más.

Si necesitas también contar filas filtradas con condiciones (por ejemplo, ventas de Norte mayores de 500), SUBTOTALES se queda corto. Ahí entran SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO (en inglés SUMIFS y COUNTIFS), que filtran por criterio sin necesidad de aplicar autofiltro.

Lo que ya sabes hacer con SUBTOTALES

• Aplicar una operación (suma, promedio, conteo, máximo, mínimo y más) solo sobre las filas visibles de un rango, sin que el resultado incluya lo que el filtro o tú habéis ocultado.
• Elegir entre los códigos 1-11 y 101-111 sabiendo que los primeros incluyen las filas ocultas a mano y los segundos las ignoran; las filas filtradas se ignoran siempre.
• Usar el botón Datos > Esquema > Subtotales para que Excel cree las filas de subtotal y los símbolos 1 2 3 del esquema automáticamente, sin escribir una sola fórmula.
• Anidar SUBTOTALES dentro de un SI con una lista desplegable para que el usuario elija la operación sin tocar la fórmula.

El siguiente paso natural es una tabla dinámica: cuando los datos crezcan o necesites cruzar región con comercial con mes, Insertar > Tablas > Tabla dinámica te da el mismo «respeto por el filtro» que SUBTOTALES, con segmentación de datos y la posibilidad de cambiar filas, columnas y valores con clics. Si te interesa automatizar el proceso para no rehacer los totales cada vez, repasa el tutorial de tablas dinámicas antes de pasar a macros con VBA.