Sumar subtotales en Excel sin contar filas ocultas

SUBTOTALES es la función de Excel pensada para resumir una lista sin que el resultado incluya las filas que has filtrado o, según elijas, las que has ocultado a mano. Con ella puedes sumar, contar, promediar, sacar el máximo o el mínimo, todo desde la misma fórmula, y el total se actualiza solo cuando aplicas o quitas un filtro. En este tutorial la vas a usar sobre la hoja de ventas de una pequeña tienda que reparte sus ventas por regiones, y al terminar sabrás decidir entre los códigos 9 y 109, compararla con SUMA y agrupar la lista con la herramienta Datos > Subtotales. SUBTOTALES está disponible en Excel para Microsoft 365, 2024, 2021, 2019 y 2016, también en sus versiones para Mac.

Qué es SUBTOTALES y cuándo suma solo lo visible

Imagina la hoja de ventas de una tienda: una lista con la región, el producto y el importe de cada venta. Al final quieres un total, pero ese total debe cambiar cuando filtras por región o cuando apartas algunas filas para revisarlas. Ahí entra SUBTOTALES (en inglés SUBTOTAL): una función que resume la lista y, además, entiende qué filas están visibles en cada momento.

La idea es sencilla: la fórmula mira la lista y, en vez de sumar siempre todas las celdas, deja fuera las filas que no pasan el filtro. Si además usas los códigos 101-111, también deja fuera las filas que ocultas tú a mano con Inicio > Formato > Ocultar y mostrar > Ocultar filas. Con los códigos 1-11, en cambio, esas filas ocultas sí cuentan.

En esta hoja, con todas las filas visibles, =SUBTOTALES(109;B2:B7) muestra 423. En cuanto filtres por una región, el resultado bajará porque las filas filtradas se quedan fuera del cálculo.

Cómo elegir el cálculo y los rangos de SUBTOTALES

La sintaxis de la función es:

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

El primer argumento, núm_función, es obligatorio y vale un número del 1 al 11 o del 101 al 111. Cada número indica una operación (suma, promedio, contar, máximo, etc.) y, además, decide si las filas ocultadas a mano entran o no. Después viene al menos un rango y, opcionalmente, hasta 254 rangos más si necesitas resumir varias columnas a la vez.

Los códigos 1-11 y 101-111

Las dos series ignoran siempre las filas filtradas. La diferencia entre usar 9 o 109 solo se nota cuando ocultas filas a mano: 9 las incluye y 109 las deja fuera.

Los rangos que entran en el subtotal

La fórmula trabaja con rangos verticales, no con referencias 3D. Para una sola columna basta con algo como:

=SUBTOTALES(109;C2:C100)

Sumar ventas visibles después de aplicar un filtro

Vamos a la práctica. Crea una hoja llamada Ventas y copia esta tabla a partir de A1:

1. Selecciona A1:C7.
2. Abre Datos > Ordenar y filtrar > Filtrar. Aparecerá una flecha en cada encabezado.
3. En la celda B9 escribe la fórmula:

=SUBTOTALES(109;B2:B7)

1. Pulsa Entrar. El resultado es 423 (120 + 10 + 150 + 23 + 80 + 40).
2. Pulsa la flecha de la columna Región y deja solo "Norte". El resultado de B9 pasa a 130.
3. Quita el filtro y vuelve a 423.

Filtrar una región y ver el total

Ahora vas a aplicar el filtro sobre la lista completa. Selecciona A1:C7, abre el menú y elige Filtrar:

Pulsa la flecha de la columna Región y deja solo "Sur". La hoja muestra las dos ventas del Sur (150 y 23), y B9 pasa a 173 sin tocar la fórmula. Cambia el filtro a "Este" y verás 80. Vuelve a mostrar todo para recuperar 423.

Comparar SUBTOTALES con SUMA

En B10 escribe la suma clásica:

=SUMA(B2:B7)

Aplica el mismo filtro por región. SUBTOTALES en B9 se adapta a las filas visibles; SUMA en B10 sigue mostrando 423 porque incluye las filas filtradas y las ocultas.

Ocultar filas a mano y controlar qué suma SUBTOTALES

Ahora la tienda quiere apartar temporalmente dos ventas para revisarlas con el proveedor, sin perderlas de la lista. Lo más cómodo es ocultar esas filas a mano y ver qué pasa con cada código.

1. Selecciona las filas 4 y 5 (las del Sur).
2. Clic derecho sobre el número de fila y elige Ocultar, o usa:

1. En B9 cambia la fórmula a la versión que incluye las ocultas:

=SUBTOTALES(9;B2:B7)

1. En B10 deja la versión que las excluye:

=SUBTOTALES(109;B2:B7)

Con las filas del Sur ocultas, B9 muestra 250 (120 + 10 + 80 + 40) y B10 muestra 100 (120 + 10 + 40 + 40, espérate: 10 + 80 + 40, sin las del Sur). La diferencia entre 9 y 109 aparece solo cuando ocultas filas a mano; con un filtro las dos se comportan igual.

Elegir entre 9 y 109 para una suma

Si el equipo revisa ventas y las aparta temporalmente, lo normal es no querer que esas ventas alteren los totales: usa 109. Si en cambio las filas ocultas son una “vista” sin más (por ejemplo, plegadas para imprimir), 9 refleja mejor la suma completa de la lista.

Usar Subtotales para agrupar una lista por región

Además de la función, Excel trae una herramienta que crea los subtotales por ti y los mete dentro de la lista. Le viene muy bien a la tienda para ver, de un vistazo, cuánto vendió en cada región.

1. Ordena la lista por la columna Región. Selecciona A1:C7 y abre:

1. Elige ordenar por Región de A a Z y pulsa Aceptar.
2. Con A1:C7 seleccionado, abre:

1. Rellena el cuadro:

1. Pulsa Aceptar. Excel inserta una fila bajo cada región con la suma de sus ventas y, al final, una fila con el total general (423).

A la izquierda de la hoja aparece un panel con niveles 1, 2 y 3: el 1 muestra solo el total general, el 2 las filas de cada grupo y el 3 toda la lista.

Cambiar la función del subtotal creado

Para practicar con otra operación, vuelve a abrir Datos > Subtotales, cambia Usar función a Promedio y marca Reemplazar subtotales actuales. La columna Venta mostrará ahora el promedio de cada región (por ejemplo, 80 para Este, 65 para Norte, 40 para Oeste y 86,5 para Sur). Si vuelves a Suma, recuperas los subtotales anteriores.

Por qué SUBTOTALES no suma los datos esperados

La función es robusta, pero tres tropiezos frecuentes la hacen parecer rota: un número guardado como texto, una lista en horizontal y subtotales anidados que se pisan.

Un número escrito a la izquierda no entra en la suma

Si alguien teclea una venta con el teclado en otro idioma o le pone un apóstrofo delante (“’150”), Excel la guarda como texto y la alinea a la izquierda. SUBTOTALES, como SUMA, la ignora.

La celda B6 aparece alineada a la izquierda y la fórmula de B9 devuelve 343 en lugar de 423. Para arreglarlo, pulsa en la celda, F2 para editarla y Entrar para que Excel la convierta en número; o multiplica por 1 con =B6*1 y pega el resultado como valor.

Una referencia 3D termina en #¡NUM!

SUBTOTALES está pensada para columnas o rangos verticales dentro de la misma hoja. Si le pasas una referencia 3D, devuelve el error #¡NUM!:

=SUBTOTALES(109;Hoja1:Hoja3!B2:B7)

Para resumir varias hojas, coloca SUBTOTALES en cada hoja y luego suma sus resultados en una hoja de resumen con =SUMA('Hoja1'!B9;'Hoja2'!B9;'Hoja3'!B9).

Subtotales anidados dentro del mismo rango

Si usas la herramienta Datos > Subtotales sobre una lista que ya tiene filas de total, la segunda pasada las reemplaza si marcas Reemplazar subtotales actuales. Si no la marcas, Excel las conserva y SUBTOTALES las omite al calcular para no duplicar resultados. La regla práctica: aplica Subtotales una vez por nivel y decide el orden (por región, luego por producto, etc.) antes de empezar.

Dónde usar SUBTOTALES y qué versiones lo incluyen

SUBTOTALES está en Excel para Microsoft 365, 2024, 2021, 2019 y 2016, en sus versiones de Mac equivalentes y en Excel para la web. No está en Excel 2013 ni en anteriores. La herramienta Datos > Subtotales acompaña a la función desde Excel 2007.

Lo que ya sabes hacer con SUBTOTALES

• Escribir =SUBTOTALES(109;B2:B7) para sumar solo las filas visibles, filtradas u ocultas a mano.
• Cambiar a =SUBTOTALES(9;B2:B7) cuando quieres que las filas ocultas a mano sí cuenten, pero las filtradas no.
• Comparar SUBTOTALES con SUMA en celdas contiguas para ver la diferencia entre “lo que se ve” y “todo lo de la lista”.
• Crear subtotales por grupo con Datos > Subtotales, eligiendo Suma, Promedio u otra operación y la columna a resumir.
• Quitar todos los subtotales cuando ya no los necesitas, con el botón Quitar todos del mismo cuadro.
• Detectar números guardados como texto por su alineación a la izquierda y reconvertirlos antes de calcular.

El siguiente paso natural es aplicar formato condicional a la columna Venta para destacar de un vistazo los importes más altos; así, la lista gana una capa visual que se entiende sin leer cada fila.

Dudas al sumar subtotales en Excel