SUBTOTALESen Excel.
Filtra tu reporte. El total te sigue.
Obtén las ventas de Norte sin rehacer la fórmula. Cambia de región, oculta una fila y descubre qué código necesitas para que el subtotal responda a tu reporte.
=SUBTOTALES(9;D2:D7)| Fila | Región · Producto | D · Ventas | Destacada |
|---|---|---|---|
| 2 | Norte · Laptop | $1,200 | Destacada |
| 3 | Sur · Mouse | $900 | |
| 4 | Norte · Teclado | $750 | Destacada |
| 5 | Centro · Monitor | $650 | |
| 6 | Norte · Audífonos | $500 | Destacada |
| 7 | Sur · Webcam | $400 |
VISTA RESUMIDA · LA TABLA COMPLETA A:D ESTÁ EN LA PRÁCTICA
¿Para qué sirve SUBTOTALES en Excel?
SUBTOTALES resume datos de una lista y responde al filtro que aplicas. Puedes sumar, contar, promediar y realizar otras operaciones mediante un código. Es útil cuando el mismo reporte debe mostrar resultados por región, vendedor o producto sin editar la fórmula cada vez.
La infografía #036 parte de seis ventas que suman 4,400. Al filtrar Región = Norte quedan los importes 1,200, 750 y 500: el subtotal es 2,450. SUMA(D2:D7) conserva 4,400 porque incluye también las filas que el filtro no muestra.
Sintaxis de SUBTOTALES y argumentos
SUBTOTALES(núm_función; ref1; [ref2]; …)| Argumento | Valor | Qué hace |
|---|---|---|
| núm_función | 9 | Suma; excluye filas filtradas e incluye las ocultas manualmente. |
| ref1 | D2:D7 | Usa las seis celdas de Ventas, sin incluir el encabezado. |
| Filtro de la hoja | Región = Norte | Se aplica a los datos. No es un argumento de la fórmula. |
Pruébalo: ventas por región y filas ocultas
Empieza con los datos y el filtro del original. Después oculta manualmente la fila 2 y cambia de 9 a 109: con Norte seleccionado, pasarás de 2,450 a 1,250. Quitar Laptop resta sus 1,200 solo cuando el código excluye las filas ocultas manualmente.
Cómo sumar datos filtrados con SUBTOTALES
- Copia la tabla y pégala en A1 de una hoja vacía. Los encabezados son ID, Región, Producto y Ventas; los registros ocupan A2:D7.
- Selecciona A1:D7 y activa Datos > Filtro. En la flecha de Región, deja seleccionada Norte.
- En F2, fuera de las filas de datos de la tabla, escribe =SUBTOTALES(9;D2:D7). Si tu configuración separa argumentos con coma, usa =SUBTOTALES(9,D2:D7).
- Comprueba 1,200 + 750 + 500 = 2,450. Cambia el filtro a Sur: el resultado será 1,300. Al mostrar todas las regiones, será 4,400.
- Para excluir también una fila oculta manualmente, sustituye 9 por 109. Comprueba la regla antes de compartir el indicador.
=SUBTOTALES(109;D2:D7)Diferencia entre 9 y 109, y otros códigos útiles
| Operación | Incluye ocultas manuales | Excluye ocultas manuales |
|---|---|---|
| PROMEDIO | 1 | 101 |
| CONTAR (números) | 2 | 102 |
| CONTARA (contenido) | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| SUMA | 9 | 109 |
Estos son seis pares de uso habitual; la función admite códigos del 1 al 11 y del 101 al 111. Los de la segunda serie omiten también las filas ocultas manualmente. Usa rangos verticales: ocultar una columna no equivale a ocultar una fila para este cálculo.
Casos de uso: ventas, inventarios y seguimiento
Una gerencia puede filtrar Norte para analizar 2,450 y cambiar a Sur para revisar 1,300 sin rehacer el resumen. Con un reporte de inventario, la misma idea sirve para sumar existencias de una categoría seleccionada.
Para un control de expedientes, los códigos 3 y 103 cuentan celdas con contenido en las filas seleccionadas. Elige el campo de referencia con cuidado: contar un identificador responde cuántos registros hay; contar el contacto responde cuántos tienen algo capturado en ese campo.
La fila de totales de una tabla de Excel puede usar SUBTOTALES para responder a sus filtros mediante referencias estructuradas. La función también puede escribirse directamente en un rango normal. El comando Datos > Subtotal es una herramienta distinta para insertar resúmenes por grupos.
Errores frecuentes al calcular subtotales
| Situación | Revisión |
|---|---|
| Oculté una fila y sigue sumando | Con código 9 es esperado. Usa 109 si necesitas excluir la fila oculta manualmente. |
| Filtré Norte pero el resultado sigue en 4,400 | Comprueba que la celda utiliza SUBTOTALES y que el filtro abarca todas las filas del rango D2:D7. |
| Un importe no entra en el subtotal | Revisa si está almacenado como texto, además del filtro y las filas ocultas. |
| Ya existen subtotales dentro del rango | SUBTOTALES omite otras fórmulas SUBTOTALES referenciadas para evitar duplicarlas. Esa regla no elimina automáticamente cualquier total escrito como número. |
| La fórmula incluye su propia celda | Coloca el resultado fuera de D2:D7 para evitar una referencia circular. |
Resuelve tus dudas
Preguntas frecuentes sobre SUBTOTALES
¿Cuál es la diferencia entre SUBTOTALES 9 y 109?
Los dos suman y excluyen las filas fuera del filtro. El 9 incluye las filas ocultas manualmente; el 109 también las excluye. Si no ocultaste filas a mano, ambos dan el mismo resultado.
¿Por qué SUMA no cambia cuando filtro datos?
SUMA incluye los valores de las filas filtradas. En el caso original sigue dando 4,400; SUBTOTALES con Norte seleccionado devuelve 2,450.
¿Puedo contar filas visibles con SUBTOTALES?
Sí. Usa 2 o 102 para contar celdas numéricas, y 3 o 103 para contar celdas con contenido. La segunda opción de cada par excluye también las filas ocultas manualmente.
¿SUBTOTALES funciona sin convertir el rango en tabla?
Sí. El ejemplo usa D2:D7 en un rango normal con filtro. Las tablas de Excel también pueden utilizar la función en su fila de totales.
¿Qué pasa si el filtro no devuelve ninguna venta?
Al sumar con 9 o 109, el resultado es 0 cuando no queda ningún importe incluido. En la práctica puedes seleccionar Este para comprobar ese caso; la regla de otras operaciones, como promediar, puede ser diferente.
Elaborado por Excelerizate a partir de la guía Visual #036. Los datos se usan con fines didácticos.