SUBTOTALES en Excel: totales sin subtotales ni filtros

=SUBTOTALES(9;C2:C8) suma los números de C2:C8, como SUMA, con dos diferencias: deja fuera cualquier otra fórmula SUBTOTALES del rango y deja fuera las filas ocultas por un filtro. El primer argumento, 9, dice qué cálculo hacer. La función SUBTOTALES se llama SUBTOTAL en inglés, y la tabla muestra la fórmula así: =SUBTOTAL(9,C2:C8). La tabla también acepta las fórmulas escritas en español, con punto y coma (o con comas, como en México).

FórmulasRestablecer

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.En Excel en español: =SUBTOTALES(9;C2:C7)

El total general de C8 abarca toda la columna, filas de subtotal incluidas, y aun así muestra 445: SUBTOTALES deja fuera C4 y C7 porque contienen fórmulas SUBTOTALES. F2 hace lo mismo con SUMA y muestra 890, con cada venta contada dos veces. Con SUBTOTALES en cada fila de total puedes añadir o mover grupos sin reescribir el total general.

Números de función de SUBTOTALES

=SUBTOTAL(function_num, ref1, [ref2], ...)

Cuando escribes =SUBTOTALES(, Excel muestra esta lista, así que no necesitas memorizarla. 9 y 109 (SUMA), 1 (PROMEDIO) y 103 (contar filas visibles) son los más usados.

FórmulasRestablecer

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.En Excel en español: =SUBTOTALES(1;C2:C7)

Aquí no hay nada oculto, así que cada línea es igual a la función normal: un promedio de 88,33, 6 filas, un MAX de 200, un MIN de 30 y una SUMA de 530. La diferencia solo aparece cuando hay filas ocultas, y de eso trata la siguiente sección.

SUBTOTALES 9 o 109, y las filas filtradas

Activa un filtro con Datos > Filtro (Ctrl+Mayús+L, Cmd+Mayús+F en Mac) y elige North en el desplegable de Region. Las filas de las demás regiones se ocultan:

• =SUMA(C2:C7) sigue sumando las seis filas.
• =SUBTOTALES(9;C2:C7) y =SUBTOTALES(109;C2:C7) suman solo las filas visibles de North.
• =SUBTOTALES(103;A2:A7) cuenta las filas que quedan en pantalla: 2, el mismo número de registros encontrados que indica la barra de estado.

Las dos familias solo se diferencian en las filas que ocultas a mano (selecciona filas, clic derecho > Ocultar). 9 las sigue sumando; 109 no. Si el total siempre debe coincidir con lo que se ve en pantalla, usa 109. Si ocultas filas solo para ordenar la vista y aún quieres que cuenten en el total, usa 9.

SUBTOTALES solo trabaja con filas. Las columnas ocultas siempre se incluyen, así que =SUBTOTALES(109;B2:G2) a lo largo de una fila también suma las columnas ocultas.

La forma más rápida de obtener un SUBTOTALES es el botón Autosuma con un filtro activado: Excel escribe =SUBTOTALES(9;...) en lugar de SUMA. Datos > Subtotal va más allá: en una lista ordenada por una columna, inserta una fila de total debajo de cada grupo y un total general, todos con SUBTOTALES, además de botones de esquema para contraer los grupos.

AGREGAR: un SUBTOTALES que puede saltar errores

Si una celda del rango contiene un error, SUMA y SUBTOTALES devuelven ese error. AGREGAR (AGGREGATE en inglés, Excel 2010 y posteriores) es SUBTOTALES con un argumento de opciones más; la opción 6 omite los valores de error.

FórmulasRestablecer

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.En Excel en español: =AGREGAR(9;6;C2:C7)

C3 contiene #N/A, así que F2 también muestra #N/A. F3 lo omite y suma los otros cinco: 450. Su primer argumento usa los mismos números que SUBTOTALES (9 es SUMA, 4 es MAX). Otras opciones: 5 omite las filas ocultas, 7 omite las filas ocultas y los errores, 3 omite las filas ocultas, los errores y las fórmulas SUBTOTALES y AGREGAR anidadas. Cambia C3 por un número y F2 muestra el mismo total que F3.

Práctica: un total general sobre subtotales

FórmulasRestablecer

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

Haz clic en una celda para ver su fórmula. Cambia un número o una fórmula y la hoja se recalcula.

Tu turno: La lista tiene un subtotal debajo de cada región. Pon en C9 un total general que abarque C2:C8 sin contar dos veces las filas de subtotal.

ComprobarVer respuesta

Por qué un total con SUBTOTALES sigue saliendo mal

• Los totales de grupo usan SUMA. SUBTOTALES deja fuera otras fórmulas SUBTOTALES de su rango, no las fórmulas SUMA. Un total de grupo escrito como =SUMA(C2:C3) se vuelve a contar. Cambia todas las filas de total a SUBTOTALES.
• Las filas se ocultaron a mano y el número de función es 9. Usa 109.
• Los datos están en columnas, no en filas. Las columnas ocultas nunca se dejan fuera.
• Necesitas una condición, no un filtro. SUBTOTALES sigue lo que oculta el filtro. Para totalizar North sin filtrar, usa SUMAR.SI. Para un resumen de todos los grupos a la vez, una tabla dinámica lo hace sin filas de total en los datos.