Aplica un filtro a tus datos de Excel, pero la función SUM sigue calculando filas ocultas. Esto ocurre porque la función SUM estándar incluye todas las celdas de su rango, independientemente de su visibilidad. Excel proporciona una función dedicada, SUBTOTAL, diseñada para realizar cálculos sobre datos filtrados. Este artículo explica cómo usar SUBTOTAL para sumar solo celdas visibles y aclara cuándo usarlo en lugar de SUM.
Puntos clave: Sumar datos filtrados en Excel
- Función SUBTOTAL con 109: Calcula una suma que ignora automáticamente las filas ocultas por un filtro.
- Botón Autosuma después de filtrar: Inserta la función SUBTOTAL automáticamente al sumar una columna filtrada.
- Función SUM: Suma todos los números en un rango, incluidos los de filas ocultas por el filtro.
Por qué SUM no funciona con datos filtrados
La función SUM es una herramienta aritmética básica. Suma todos los valores numéricos que especifiques dentro de su rango. No evalúa el estado de la hoja de cálculo. Cuando ocultas filas usando un filtro, Excel solo cambia su propiedad de visualización. Los datos en esas filas siguen siendo parte de la hoja de cálculo y, por lo tanto, parte de cualquier rango referenciado por SUM. Por eso una fórmula SUM continúa mostrando el total de todos los datos, dando resultados incorrectos para tu vista filtrada. Para realizar cálculos que respondan al filtrado, necesitas una función que pueda ignorar filas ocultas.
Pasos para sumar celdas visibles con SUBTOTAL
La función SUBTOTAL es la herramienta correcta para esta tarea. Utiliza un número de función para especificar el tipo de cálculo y, por defecto, excluye los valores en filas ocultas por un filtro.
Método 1: Usar la función SUBTOTAL directamente
- Selecciona la celda para tu total
Haz clic en la celda donde deseas que aparezca la suma de las filas visibles, típicamente debajo de tus datos filtrados. - Escribe la fórmula SUBTOTAL
Escribe =SUBTOTAL( y luego ingresa el número de función. Para una suma que ignore filas filtradas, usa 109. La fórmula debería verse así: =SUBTOTAL(109, - Selecciona tu rango
Con el mouse, selecciona el rango de celdas que deseas sumar, como B2:B100. La fórmula ahora se verá =SUBTOTAL(109,B2:B100). - Completa la fórmula
Escribe un paréntesis de cierre ) y presiona Enter. La celda mostrará ahora una suma que se actualiza automáticamente cuando cambias el filtro.
Método 2: Usar el botón Autosuma en una columna filtrada
- Aplica tu filtro
Selecciona tu rango de datos y haz clic en Datos > Filtro para habilitar el filtrado. Usa las flechas desplegables para filtrar tus datos según sea necesario. - Selecciona la celda debajo de los datos visibles
Haz clic en la celda vacía directamente debajo de la columna de números que deseas sumar. - Haz clic en el botón Autosuma
Ve a la pestaña Inicio en la cinta de opciones. En el grupo Modificar, haz clic en el botón Autosuma (el icono sigma griego Σ). - Confirma la fórmula
Excel insertará automáticamente una función SUBTOTAL, no una función SUM, y resaltará el rango de celdas visibles. Presiona Enter para confirmar.
Errores comunes y limitaciones
Usar el número de función SUBTOTAL incorrecto
SUBTOTAL tiene dos conjuntos de números de función. Los números 1-11 incluyen valores en filas ocultas. Los números 101-111 ignoran valores en filas ocultas. Para sumar listas filtradas, usa siempre 109 o 9. Usar 9 funcionará, pero es del conjunto anterior que incluye filas ocultas manualmente. Para consistencia con filtros, 109 es la mejor opción.
SUBTOTAL ignora otros SUBTOTAL
Un diseño clave de la función SUBTOTAL es evitar el doble conteo. Si tu rango seleccionado contiene otras celdas con fórmulas SUBTOTAL, esas celdas se excluyen automáticamente del nuevo cálculo SUBTOTAL. Esto es intencional y evita que los totales incluyan otros totales.
Filas ocultas manualmente vs. filas filtradas
La función SUBTOTAL con el argumento 109 ignora las filas ocultas por un filtro. Sin embargo, si haces clic derecho en una fila y seleccionas Ocultar, esa fila está oculta manualmente. SUBTOTAL con 109 aún incluirá valores de filas ocultas manualmente. Solo los números de función más antiguos (1-11) ignoran filas ocultas manualmente, pero incluyen filas filtradas.
SUBTOTAL vs SUM: Diferencias clave
| Elemento | SUBTOTAL | SUM |
|---|---|---|
| Uso principal | Cálculos en listas filtradas o con subtotales | Suma básica de todos los números en un rango |
| Respuesta al filtro | Ignora automáticamente las filas ocultas por un filtro | Incluye todas las filas, independientemente del filtro |
| Números de función | Usa números (ej., 109 para suma) para especificar el tipo de cálculo | Sin números de función; solo una operación |
| Comportamiento de anidamiento | Ignora otras celdas SUBTOTAL dentro de su rango | Sumará cada celda, incluyendo otras fórmulas SUM |
| Ocultamiento manual de filas | El argumento 109 incluye filas ocultas manualmente | Incluye filas ocultas manualmente |
Ahora puedes usar la función SUBTOTAL para crear totales precisos en tus informes filtrados. Recuerda usar el número de función 109 para una suma que respete tus filtros activos. Para tu próxima tarea, intenta usar SUBTOTAL con otros números de función como 101 para PROMEDIO o 103 para CONTARA en datos filtrados. Un consejo avanzado útil es nombrar tu rango de datos como una Tabla usando Ctrl+T; esto permite que tus fórmulas SUBTOTAL se expandan automáticamente cuando agregas nuevas filas de datos.