Cómo sumar o contar tablas que han sido filtradas?

Ésta es una de esas preguntas que de vez en cuando uno se hace cuando se está trabajando con Excel. Si tengo una tabla con filtros, y la filtro por una o varias de sus variables, cómo puedo contar o sumar las celdas resultantes?


Una pregunta muy bien dirigida, y por supuesto que con una respuesta todavía mejor.

Lo primero, prepárense para un viaje intenso por el maravilloso mundo de la fórmulas en Excel, elijan un té o un café y allá vamos.

Imaginemos que tenemos una tabla definida en el rango A2:D12, esta tabla tiene fechas, países, producto y un valor cualquiera. Also así:



Ahora imaginemos que lo que queremos hacer es contar el número de veces que un tipo de producto aparece en la tabla cada vez que utilizamos un filtro, o sumar los valores de un tipo de producto cuando se ha filtrado la tabla.


En esta tabla hay sólo 2 productos B1, que suman 73, uno está en Nigeria y otro está en Argentina.

Ahora, si filtramos la tabla por Argentina, queremos una fórmula que nos devuelva 1 por el recuento de productos B1 y 59 la suma de los valores. Y esto cómo lo hacemos?

Solución

La fórmula ha utilizar será:

Para el recuento: =SUMAPRODUCTO(SUBTOTALES(103,DESREF(A1,FILA(A2:A12)-FILA(A1),0)),--(C2:C12="B1"))

Para la suma de valores: =SUMAPRODUCTO(SUBTOTALES(103,DESREF(A1,FILA(A2:A12)-FILA(A1),0)),--(C2:C12="B1"),D2:D12)

------------------------------------------------------------------------------------------------------------------------

RECUERDA QUE:

Tú puedes colaborar para seguir manteniendo este blog de una manera gratuita haciendo varios clics en los anuncios de la página. Como referencia, 2 clics por artículo. Muchas gracias por querer colaborar.

------------------------------------------------------------------------------------------------------------------------

Recuento

Ahora explicaremos esta fórmula por partes. Primero nos concentraremos en la fórmula del recuento, ya que entendiendo ésta será mucho más fácil entender la fórmula para la suma de los valores.

Lo primero que vemos es que estamos usando la función SUMAPRODUCTO. Esta función devuelve la suma de los productos de varios rangos o matrices. Así que estamos multiplicando 2 rangos. Y cuáles son?

Rango 1: SUBTOTALES(103,DESREF(A1,FILA(A2:A12)-FILA(A1),0))

Rango 2: --(C2:C12="B1")

Vamos a ver cada rango.

Rango 1: SUBTOTALES(103,DESREF(A1,FILA(A2:A12)-FILA(A1),0))

La función SUBTOTALES devuelve un subtotal en una lista o base de datos. Esta función puede funcionar con tablas filtradas.

Sintaxis:  SUBTOTALES(núm_función, ref1, [ref2], …])

• núm_función (Obligatorio): Número entre 1 y 11 o entre 101 y 111 que indica la función de Excel a utilizar. Mira la lista abajo.
• ref1 (Obligatorio): El primer rango o celda a considerar en la operación.
• ref2 (Opcional): El segundo rango o celda a considerar en la operación hasta un máximo de 254 rangos.

NÚM_FUNCIÓN (INCLUYE VALORES OCULTOS)	NÚM_FUNCIÓN (PASA POR ALTO VALORES OCULTOS)	FUNCIÓN
1	101	PROMEDIO
2	102	CONTAR
3	103	CONTARA
4	104	MAX
5	105	MIN
6	106	PRODUCTO
7	107	DESVEST
8	108	DESVESTP
9	109	SUMA
10	110	VAR
11	111	VARP

Si ahora miramos SUBTOTALES(103, rango), ésta contara los valores visibles en una tabla filtrada.

El rango en esa fórmula está construido por la función DESREF.

Veamos qué hace DESREF(A1,FILA(A2:A12)-FILA(A1),0).

La función DESREF devuelve un rango a partir del criterio dado. En nuestro caso, creará un rango de una columna y varias filas.

Rompamos ahora FILA(A2:A12)-FILA(A1), para entender que rango tenemos.

Esta fórmula es una fórmula de matriz, y Excel la tratará así:

FILA(A2)-FILA(A1)=1

FILA(A3)-FILA(A1)=2

FILA(A4)-FILA(A1)=3

...

FILA(A12)-FILA(A1)=11

Así que la fórmula creará un rango {1;2;3;4;5;6;7;8;9;10;11}.

Ahora vamos a DESREF(A1,FILA(A2:A12)-FILA(A1),0), que como acabamos de ver será DESREF(A1,{1;2;3;4;5;6;7;8;9;10;11},0).

DESREF(A1,1,0) = A2

DESREF(A1,2,0) = A3

...

DESREF(A1,11,0) = A12

Con lo que vemos que la fórmula de  SUBTOTALES(103,DESREF(A1,FILA(A2:A12)-FILA(A1),0)) en realidad está calculando lo siguiente:

SUBTOTALES(103,{A2, A3, A4, A5, A6, A7, A8, A9, A10, A11, A12}) que devolverá qué celdas entre A2:A12 son visibles. Lo que hará será contar las celdas visibles una a una en ese rango, creando un rango de 1s y 0s. Algo así {1,1,0,0,0,1,0,0,0,0,1}, que sería que las celdas A2, A3, A7, A12 son visibles, si el filtro aplicado a país es Argentina.

Rango 2: --(C2:C12="B1")

Como se puede apreciar esto devolverá otro rango, esta vez lo que está haciendo es lo siguiente:

C2="B1" = FALSO

C3="B1" = FALSO

...

C12="B1" = FALSO

(C2:C12="B1") devuelve un rango así: = {FALSO,FALSO,FALSO,VERDADERO,FALSO,VERDADERO,FALSO,FALSO,FALSO,FALSO,FALSO}

Al poner -- delante de la fórmula, lo que estamos haciendo en convertir VERDADERO = 1 y FALSO = 0, de tal manera que el rango queda así: --(C2:C12="B1") = {0,0,0,1,0,1,0,0,0,0,0}

Asi que al final tenemos que SUMAPRODUCTO  en Excel está utilizando esos 2 rangos:

Rango 1= {1,1,0,0,0,1,0,0,0,0,1} si las celdas A2, A3, A7, A12 son visibles.

Rango 2= {0,0,0,1,0,1,0,0,0,0,0} si la condición es que el producto sea "B1".

Multiplicando los 2 rangos y sumamos los productos: Rango 1 x Rango 2 = {1,1,0,0,0,1,0,0,0,0,1} x {0,0,0,1,0,1,0,0,0,0,0} ={1x0 + 1x0 + 0x0 + 0x1 + 0x0 + 1x1 + 0x0 +0x0 +0x0 +0x0 +1x0} = 1

Soy hay una celda visible que tenga el producto B1.

Suma

Acabamos de ver cómo se hace el recuento en tablas filtradas. Ahora queremos ver cómo se realiza la suma en tablas filtradas.

=SUMAPRODUCTO(SUBTOTALES(103,DESREF(A1,FILA(A2:A12)-FILA(A1),0)),--(C2:C12="B1"),D2:D12)

En el recuento hemos visto cómo funciona la mayor parte de esa fórmula. En realidad lo que estamos haciendo ahora en añadir otro rango:

Rango 1= {1,1,0,0,0,1,0,0,0,0,1} si las celdas A2, A3, A7, A12 son visibles.

Rango 2= {0,0,0,1,0,1,0,0,0,0,0} si la condición es que el producto sea "B1".

Rango 3= {"Valor",71,37,14,61,59,59,63,57,26,21}, que son todos los valores en D2:D12

Asi que Rango 1 x Rango 2 X Rango 3 = {1x0x"Valor" + 1x0x71 + 0x0x37 + 0x1x14 + 0x0x61 + 1x1x59 + 0x0x59 +0x0x63 +0x0x57 +0x0x26 +1x0x21} = 59

Enlace relacionado

Las fórmulas de matriz

Aquí te puedes descargar un archivo para practicar la fórmula: Descargar archivo

Te ha gustado el artículo?

No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:

1. Puedes invitarme a un café pinchando en la siguiente imagen.

2. Puedes hacer clic en los anuncios mostrados en la página

3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.

Quieres aprender más en Excel o tienes algún problema que necesita solución?

Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.

Consultoría Excel / Cursos de Excel

Subscríbete y recibe todos las entradas por email

Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales

Facebook, Twitter, Youtube y Google+