La función SUBTOTALES con criterios.

lunes, julio 16, 2012

Como ya sabemos, la función SUBTOTALES nos permite realizar cálculos con once operaciones distintas sin tomar en cuenta celdas ocultas. Esto es muy práctico cuando usamos Autofiltro



El resultado de la fórmula refleja sólo los valores de las filas visibles. Un lector me pregunta si se puede hacer el cálculo de tal manera que SUBTOTALES sólo tome en cuenta los valores positivos (o negativos, o distintos de cero, es decir, aplicando algún criterio al cálculo).

Una forma de hacerlo sería agregando una columna auxiliar y luego aplicar el filtro a la columna del criterio y a la columna auxiliar. Por ejemplo



También se puede hacer con fórmulas, que es lo que vamos a mostrar en esta nota. La ventaja de hacerlo con fórmulas es que podemos mostrar distintos resultados, por ejemplo el total de los montos positivos y el total de los negativos, en una misma vista de la hoja.

Supongamos que queremos ver el total de los montos positivos en la celda B1 y el total de los negativos en D1



La celda B1 contiene la fórmula

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1))*($B$4:$B$14>0))


La celda D1 contiene la fórmula

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1))*($B$4:$B$14<0))

Al filtrar por el criterio “a”, por ejemplo, veremos

subtcrit04

Analicemos la fórmula:

DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1) crea una matriz que contiene los valores del rango B4:B14



La expresión ($B$4:$B$14<0) crea una martiz de valores FALSO o VERDADERO



Al multiplicar ambas expresiones entre sí obtenemos un matriz donde los valores negativos han sido remplazados por ceros



Usamos estas expresiones como argumentos de la función SUBTOTALES con el operador 9 (suma).

La función SUMAPRODUCTO opera en forma matricial, por lo que nos permite realizar todas estas operaciones en una única celda.

Para calcular el subtotal de los valores negativos usamos ($B$4:$B$14<0); si quisiéramos hacer cálculos que no incluya ceros usaríamos ($B$4:$B$14<>0).

42 comments:

Juan R Garces 17 julio, 2012 18:06  

Buenos días. Excelente Sr. Jorge, sencillamente lo que necesitaba. Es un usted muy amable en compartir sus conocimientos y ayudarnos en nuestras inquietudes. Mil y mil gracias.

Saludos

Juan R Garces.

Anónimo,  06 agosto, 2012 11:51  

Muy interesante. Gracias por sus aportaciones.

Pablo | profesor de Excel 28 febrero, 2013 16:53  

Excelente aporte Jorge. Mi pregunta es: ¿Se podría hacer con un sumar.si y posteriormente añadir el autofiltro?

Jorge L. Dunkelman 02 marzo, 2013 20:26  

Tal vez se podría usar SUMAR.SI en lugar de SUMAPRODUCTO pero no puedes prescindir de SUBTOTALES para sumar sólo celdas visibles.

Maria,  20 diciembre, 2013 16:09  

Nota super interesante. La he aplicado y me está ayudando a resolver un problema, pero en mi caso tengo una pequeña diferencia con tu ejemplo.
Uso la función SUBTOTALES en la columna E el valor al que aplico la condición está en la columna C.

En concreto sumo todos los importes de E y les resto todos los costes de E. La columna C me dicen si son costes o ingresos.

Mi fórmula es
=SUMAPRODUCTO(SUBTOTALES(9;DESREF(E$2;FILA($E$2:$E19)-FILA($E$2);;1))*(C$2:C19="Ingresos"))-SUMAPRODUCTO(SUBTOTALES(9;DESREF(E$2;FILA($E$2:$E19)-FILA($E$2);;1))*(C$2:C19<>"Ingresos"))


El problema que tengo es que, si filtro por la columna C, todo funciona bien, pero si filtro por la columna B, por ejemplo, desaparece la fila de los subtotales....

Ves algo raro en la fórmula. Muchas gracias

Jorge Dunkelman 20 diciembre, 2013 18:35  

Hola,
si la fila de subtotales está por encima de la tabla, como en mi ejemplo, no veo como puede desaparecer. Te sugiero que me envíes tu cuaderno, o un ejemplo con el mismo problema para quepueda hacerme una idea más precisa del problema. La idrección del mail e instrucciones puedes ver en el enlace Ayuda en la parte superior del blog.

María,  23 diciembre, 2013 10:56  

Hola Jorge, bueno, yo el subtotal no lo pongo por encima de la tabla, eso no lo hice igual. Lo pongo bajo las columnas que me interesa sumar...
Te envié un Excel con mi ejemplo.
Muchas gracias, María

Roger Guerrero 21 marzo, 2014 19:56  

Hay un problema con los subtotales. Funciona bien con los filtros o autofiltros, pero no cuando escondo una fila manualmente. ¿Hay alguna forma de que funcione? ¿Existe otra formula para eso?
Gracias de antemano por tu respuesta
Saludos

Jorge Dunkelman 23 marzo, 2014 07:06  

Roger, la función SUTOTALES tiene dos grupos de argumentos para definir la operación a usar: de 1 a 11, para el so con Autofiltro y de 101 a 111 para filas ocultadas manualmente.

Antonio H Cruz 08 octubre, 2014 21:18  

Buenas tardes:

He revisado variadas alternativas, visitado algunos foros donde explican el uso de DESREF de manera somera y otras opciones, ninguno me funcionó al 100, explico.
La tarea es tener un conjunto de datos para graficar que muestren el acumulado de facturación en el tiempo pero filtrando por proveedor. Una función SUBTOTALES.SI.CONJUNTO hubiera sido ideal, pero no existe.
Por lo tanto, la excelente explicación, el uso de recursos gráficos y su conocimiento me han ayudado a tener esta solución, que es la más óptima.
Sufro un poco por la velocidad de procesamiento ya que es una gran base de datos, pero eso ya es lo de menos. Generalmente no escribo en foros, pero esta vez lo hice con el fin de decir GRACIAS.

Jorge Dunkelman 08 octubre, 2014 23:22  

Antonio, gracias por los conceptos.
Tengo la impresión que hay otras soluciones más eficientes a lo que querés hacer. Estásinvitado a contactarme en forma privado (fijate en el enlace Ayuda, en la parte superior del blog).

Anónimo,  21 octubre, 2014 07:28  

Hola, buenos días, mi problema es que pongo la formula de subtotales y suma todo lo que tiene que sumar, pero cuando filtro por nombre de persona, que esta en otra columna que las cifras, me desaparece la casilla de subtotales. Me podeis ayudar? =SUBTOTALES(9;O3:O18)
Me explico, tengo dos columnas, una con los nombre y otra con las cifras, pongo la formula y suma bien, pero a la que filtro por un nombre, la celda con la suma total o subtotal no aparece

Jorge Dunkelman 22 octubre, 2014 17:41  

Cuando aplicas Autofiltro, Excel oculta todas las filas de que no cumplen con la condición elegida, no sólo las de la columna en cuestión. Por ese motivo conviene poner los totales calculado con SUBTOTAL en alguna fila por encima de la tabla a filtras o, mejor aún, usar Tablas.

ZONA SUR 26 febrero, 2015 04:57  

Me sirvió mucho la explicación para lo que necesitaba. Cambie la formula agregando valores de texto y utilice la función CONTARA en subtotales.

Muchas gracias

Anónimo,  10 marzo, 2015 17:50  

Me quito el sombrero.

guillermo 23 abril, 2015 17:22  

¿porqué la formula no funciona cuando en subtotales se pone el 1 para calcular el promedio en lugar del 9 para calcular la suma? He probado el propio ejemplo y al poner el número de función 1, aparece #¡div0!

Jorge Dunkelman 24 abril, 2015 09:33  

Guillermo, el código de cada operación fue establecido por los programandores de Microsoft. No creo que el número de operación en si mismo tenga algún significado, aunque intituivamente atribuinos 1 a la suma por ser la operación mpas frecuente.
El número de función 1 es PROMEDIO; el promedio es una división y si el divisr el 0, entonces el resultado será #¡DIV0!.

Jesús 09 mayo, 2015 21:28  

Buenas tardes. Les agradecería mucho si pueden ayudarme:
Tengo un filtro con rentabilidades con este formato (sin porcentaje)
3
-3
5
8
7
...
y quiero aplicar interés compuesto cuando hago filtros. Estoy intentándolo con VF.PLAN combinandolo con SUBTOTALES Y PRODUCTO, incluso introduciendo fórmulas matriciales y no puedo, da error.
¿Pueden ayudarme por favor?

Debería funcionar así en forma matricial pero da error:

SUBTOTALES(106; (B5:B100)/100+1)

o VF.PLAN(1;B5:B100) pero en este caso solo debería de calcularse con las celdas que no ha ocultado el filtro.
Incluso: {PRODUCTO((B5:B100)/100+1)} pero igual que en el caso anterior, no puedo quitar las celdas ocultas

Jorge Dunkelman 10 mayo, 2015 17:15  

Jesús, fijate en las instrucciones que aparecen en el enlace Ayuda (en la parte superior del blog).

Flores de Newton 23 junio, 2015 06:31  

Hola, tengo una tabla con una columna A de fechas (un año) y otra columna B con números aleatorios del 0 al 99. Necesito que al poner el filtro en la columna de fechas (ejemplo ultima semana, o últimos 3 meses) poder utilizar la fórmula CONTAR.SI para contar la solo las celdas visibles después de aplicar el filtro que cumplan con un criterio de la columna B (Ejemplo cantidad de veces que aparece el número 10 en las celdas visibles). Entiendo que la función subtotales omite valores ocultos pero esta solo me permite utilizarla con CONTAR, y no con CONTAR.SI... AYUDENME POR FAVOR!

Jorge Dunkelman 23 junio, 2015 10:30  

Flores, no queda claro. Si estás filtrando de acuerdo a un valor (por ejemplo, 10), la cuenta de las filas visibles te da el número de ocurrencias de ese valor. No hace falta CONTAR.SI.
Podés usar CONTAR.SI para contar las ocurrencias de un valor sin necesidad de filtrar.

Anónimo,  05 septiembre, 2015 20:32  

Hola buenas tarde, mi pregunta es tengo una hoja de calculo donde manejo USD y MXN por lo que puse en mi columna tres filas para valores de dolares, pesos y la sumatoria, utilizando esta formula para pesos y dolares es =SUMAR.SI(G12:$G$185,$F$6,$H$12:$H$185) donde ,$F$6, es USD o MXN, pero no se como utilizar subtotal para que solo sume pesos o dolares cuando se necesite, por su atención muchas gracias

Jorge Dunkelman 06 septiembre, 2015 16:08  

De acuerdo a tu definición, la fórmula debe funcionar bien y dar el resultado en la celda donde la haya introducido.

GEORGINA,  07 septiembre, 2015 20:44  

yo tengo dos columnas, una con el importe y otra con el tipo de moneda y hasta abajo tengo tres celdas con esta formula:
=SUMAR.SI(Q$10:Q$317,"MXN",P$10:P$317)
=SUMAR.SI(Q$10:Q$317,"USD",P$10:P$317)
=SUMAR.SI(Q$10:Q$317,"EUROS",P$10:P$317)

mi problema es cuando quiero filtrar por proveedor. me sigue sumando todo aun que está filtrado, me sigue sumando las que están ocultas, y yo quiere que me sume solo las que están visibles... como lo puedo solucionar...

Jorge Dunkelman 08 septiembre, 2015 07:15  

Podrías usar SUMA.SI.CONJUNTO aplicando dos criterios (moneda y proveedor) para obtener los resultados sin necesidad de filtrar. La mejor solución es usar una tabla dinámica, que es mucho más eficiente.

Jorge Dunkelman 08 septiembre, 2015 07:29  

Y casi me olvido: SUBTOTALES sólo calcula las celdas visibles de manera que al filtrar obtendrías el resultado buscado (no entiendo por qué usás SUMA.SI).

GEORGINA,  08 septiembre, 2015 19:43  

Hola!
Es un catálogo de conceptos y hasta abajo viene cuanto es el costo de todo el proyecto, pero hay precios en USD, MX y EUROS, antes tenia las tres columnas, una para cada moneda y ahí si usaba SUBTOTALES, pero los usuarios se equivocaban, les es más fácil usar las dos columnas, una con el importe y una con la moneda, es por eso que SUMAR.SI me funciona, pero cuando queremos o necesitamos aplicar un filtro y saber cuanto es solo de ese filtro ya no funciona.
Nunca he usado SUMA.SI.CONJUNTO, como lo puedo hacer con dos criterios? y... ¿si me funcionaria cuando filtro?
sobre las tablas dinámicas, me encantan, pero no saben los usuarios aplicarlas y tampoco tienen el interés de aprenderlas.
Gracias

Jorge Dunkelman 09 septiembre, 2015 07:06  

Georgina, SUMA.SI.CONJUNTO es SUMA.SI con más de un criterio (fijate en la ayuda en línea de Excel). En cuanto a las tablas dinámicas, si los usuarios saben usar Autofiltro no veo por qué tendrán dificultades con el filtro de una tabla dinámica. Además, si usan Excel 2010 o 2013, puedes usar segmentación de datos (slicers) para filtrar, que es mucho más intuitivo y estético que el campo de filtro de la tabla dinámica.

Unknown 14 septiembre, 2015 19:43  

Excelente. Muchas gracias.

Anónimo,  04 noviembre, 2015 22:31  

¿Qué usas, Excel 1930?
No funciona ninguna fórmula en Office 2010.

Jorge Dunkelman 05 noviembre, 2015 07:47  

Hola Anónimo,
bueno, le han funcionado a los 16000+ personas que han leído el post hasta hoy (menos 1) y a los 29 que dejaron comentarios. Y ahora, sin ironías, estás invitado a contactarme (fijate en enlace Ayuda, en la parte superior del blog), a ver si puedo ayudarte a encontrar tu error.
Ah, la nota fue desarrollada con Excel 2010.

victor manuel morales charles 17 diciembre, 2015 02:07  

Hola buenas tardes. La que sume solo las celdas visibles pero en las celdas que va a sumar contienen fórmula y la formula de subtotal no la suma, que hago?

victor manuel morales charles 17 diciembre, 2015 02:09  

El subtotal puede sumar celdas que contienen fórmula?

victor manuel morales charles 17 diciembre, 2015 02:11  

Necesito una fórmula que me sumé las celdas visibles que contienen fórmulas

Jorge Dunkelman 17 diciembre, 2015 11:33  

Víctor Manuel, SUBTOTALES suma también celdas que contienen fórmulas. No tengo claro como está planteado tu modelo por lo que sugiero que me mandes la consulta, con el cuaderno, por mail privado (fijate en el enlace Ayuda, an la parte superior del blog).

Alex Somo 25 febrero, 2016 12:27  

Muy interesante. Si se quiere tomar en cuenta un sólo valor, sólo hay que sustituir la expresión ($B$4:$B$14<0) por ($B$4:$B$14="valor"), por poner un ejemplo de texto.

Gracias!

O_CREATE 30 marzo, 2016 13:41  

Buenas! Tengo problemon, a ver si puedes ayudarme ;D

Tengo esta formula:

=SUMAPRODUCTO(SUBTOTALES(103;DESREF(DATOS[POTENCIAL RESTANTE 2016];FILA(DATOS[POTENCIAL RESTANTE 2016])-MIN(FILA(DATOS[POTENCIAL RESTANTE 2016]));;1))*(DATOS[TIPO]="CA")*DATOS[POTENCIAL RESTANTE 2016])

Lo relevante es que la parte de (DATOS[TIPO]="CA") establece la condición para que sume, y funciona. El problema es que necesito 2 condiciones.

He intentado la formula anterior con (Y(DATOS[TIPO]="CA";DATOS[ID]=1)) pero no funciona...

Help me!!! Me salvarias la vida ;D

Jorge Dunkelman 30 marzo, 2016 16:51  

En general es muy difícil revisar una fórmula si no se le ve en el contexto del modelo. Por eso en el enlace Ayuda (en la parte superior del blog) puse las instrucciones de cómo hacerlas.
Sin ver elcuaderno no puedo ayudarte.

Mikuu Pika 13 abril, 2016 14:27  

Buenas no consigo hacer que funciones tengo una tabla con filtros donde tengo una columna que pone importe. Aqui le introduzco valores negativos como positivos. como puedo hacer que solo me sume los negativos? y que tambien solo me sume las celdas visibles no todas ya que lo tengo filtrado. muchas gracias.

Jorge Dunkelman 14 abril, 2016 07:13  

Es lo que se explica en el post.

Macarena Marzeniuk 12 julio, 2016 02:17  

HOLA JORGE NECESITO SABER COMO CONTAR CELDAS VISIBLES QUE HAN SIDO FILTRADAS.EN ELLAS HAY TEXTO QUE FORMULA DEBERIA APLICAR. REALICE VARIOS FILTROS PERO NECESITO SABER CUANTOS MASCULINOS TENGO CUANDO APLICO LA FUNCION CONTAR.SI ME SUMA TODAS LAS CELDAS Y CUANDO QUIERO APLICAR LA FUNCION SUBTOTALES ...=SUBTOTALES(102,G2:G30) EL RESULTADO QUE ME DA ES 0. QUE ESTOY HACIENDO MAL? AYUDA. POR FAVOR

Jorge Dunkelman 24 julio, 2016 22:09  

Sin ver el cuaderno no puedo decirte qué es lo que estás haciendo mal.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP