Inicio › Funciones de Excel › SUBTOTALES
Función SUBTOTALES en Excel: sumar celdas filtradas y visibles
SUBTOTALES calcula una suma, un recuento, un promedio u otra estadística de un rango, pero ignora las filas ocultas por un filtro. Filtra tu lista por una región y el total se actualiza para mostrar solo esa región. En el Excel en inglés, la función se llama SUBTOTAL.
SUBTOTALES · En esta guía
Sintaxis de SUBTOTALES
=SUBTOTALES(núm_función; ref1; [ref2]; …)| Argumento | Qué significa |
|---|---|
| núm_función | Un número que le dice a SUBTOTALES qué cálculo hacer, como 9 para SUMA o 109 para SUMA sin las filas ocultas. Mira la tabla de abajo. |
| ref1; ref2; … | El rango o los rangos sobre los que se calcula. |
Números de función
| Cálculo | Incluye filas ocultas a mano | Ignora filas ocultas a mano |
|---|---|---|
| PROMEDIO | 1 | 101 |
| CONTAR (números) | 2 | 102 |
| CONTARA (no vacías) | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCTO | 6 | 106 |
| DESVEST | 7 | 107 |
| DESVESTP | 8 | 108 |
| SUMA | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
1–11 frente a 101–111: las dos versiones ignoran las filas ocultas por un filtro. La diferencia está en las filas que ocultas tú (clic derecho › Ocultar): 1–11 las siguen incluyendo y 101–111 las dejan fuera. Si dudas, usa 101–111: siempre calculan solo lo que ves.
Practica SUBTOTALES: pruébalo tú mismo
Leer es una cosa y escribirlo es otra. Construye la fórmula de abajo paso a paso: las pistas te dicen qué escribir, y puedes hacer clic en las celdas o arrastrar sobre ellas para añadirlas, igual que en Excel.
Tarea: Suma los importes con SUBTOTALES, en B7: =SUBTOTALES(9;B2:B6)
Ejemplo: sumar una lista filtrada
| A | B | C | |
|---|---|---|---|
| 1 | Región | Vendedor | Ventas |
| 2 | Norte | Ana | 1.200 |
| 3 | Sur | Bruno | 800 |
| 4 | Norte | Carlos | 950 |
| 5 | Este | Diana | 450 |
| 6 | Sur | Ana | 700 |
| 7 | Total | 4.100 |
Sin filtro, C7 muestra 4.100, lo mismo que SUMA. Ahora filtra la columna A por "Norte" (Datos › Filtro). Las filas 3, 5 y 6 se ocultan y el total cambia a 2.150: solo se suman las filas visibles. Una =SUMA(C2:C6) normal seguiría mostrando 4.100.
Contar o promediar las filas visibles
- Contar las filas visibles:
=SUBTOTALES(103; A2:A6)cuenta las celdas visibles no vacías. Tras filtrar por Norte, devuelve 2. - Promedio de lo visible:
=SUBTOTALES(101; C2:C6)devuelve 1.075 para el Norte. - Valor visible más alto:
=SUBTOTALES(104; C2:C6)devuelve 1.200 para el Norte.
Consejo: pon las fórmulas SUBTOTALES encima de la lista en lugar de debajo. Así siguen a la vista cuando la lista crece.
SUBTOTALES ignora otros subtotales
Si un rango contiene celdas que a su vez usan SUBTOTALES, esas celdas se saltan. Así puedes poner subtotales por grupo y un total general sobre toda la columna sin contar nada dos veces. Es justo lo que hace el comando Datos › Subtotal de Excel: inserta fórmulas SUBTOTALES para cada grupo y una para el total general. La fila de totales de una tabla de Excel (Ctrl+T y luego Diseño de tabla › Fila de totales) también usa SUBTOTALES.
Errores comunes
- El total no cambia al ocultar filas a mano. Usaste 1–11. Cambia a 101–111.
- Las columnas ocultas se siguen incluyendo. SUBTOTALES solo ignora filas ocultas. No se salta columnas ocultas en un rango horizontal.
- #¡VALOR! El número de función no es uno de 1–11 o 101–111.
- También necesitas ignorar errores. Usa AGREGAR, que funciona como SUBTOTALES pero además puede saltarse los valores de error:
=AGREGAR(9; 7; C2:C6)
Preguntas frecuentes
¿Qué diferencia hay entre SUBTOTALES 9 y 109?
Las dos suman solo las filas visibles después de filtrar. 109 también se salta las filas que has ocultado a mano; 9 las sigue incluyendo.
¿Cómo sumo solo las celdas visibles en Excel?
Usa =SUBTOTALES(109; rango). Ignora las filas ocultas por un filtro y las ocultas a mano.
¿Por qué usar SUBTOTALES en lugar de SUMA?
SUMA siempre suma todas las celdas del rango. SUBTOTALES respeta los filtros y se salta otros subtotales, así que da el total correcto de lo que esté visible en cada momento.
¿Qué es AGREGAR?
AGREGAR es una versión más potente de SUBTOTALES, disponible desde Excel 2010. Ofrece más cálculos y también puede ignorar los valores de error.