Suma condicional de fechas en Excel

Este post viene a colación de una consulta sobre cómo totalizar valores por fechas con más de una condición, más específicamente, entre dos fechas.
Excel pone a nuestra disposición varias herramientas: fórmulas, tablas y tablas dinámicas. Hacer el cálculo con tablas o tablas dinámicas no ofrece mayores inconvenientes. Por ejemplo, si hemos organizado los datos en forma de Tabla, podemos usar el filtro y la opción de Fila de Totales para ver el total de los valores del primer semestre del año

En una tabla dinámica creada con los mismos datos aplicamos también el filtro de fechas

Si queremos o tenemos que usar fórmulas, nos toparemos con dos "bemoles":

1. la forma en que Excel maneja la fechas dentro de las fórmulas (tema que ya tratamos en el pasado remoto de este blog);
2. el uso de operadores del tipo "mayor que", "igual", etc. dentro de fórmulas.

Si queremos hacer con funciones el cálculo mostrado más arriba podemos usar SUMAPRODUCTO o SUMAR.SI.CONJUNTO, ya que tenemos dos condiciones.

Con SUMAPRODUCTO usamos 

=SUMAPRODUCTO((B3:B62>=FECHA(2016,1,1))*(B3:B62<=FECHA(2016,3,31))*C3:C62)

La función FECHA convierte la fecha del criterio, por ejemplo 01/01/2016 en su número de serie (42370) ya que esa es la forma en que Excel maneja las fechas.

Si queremos usar SUMAR.SI.CONJUNTO usamos

=SUMAR.SI.CONJUNTO(C3:C62,B3:B62,">="&FECHA(2016,1,1),B3:B62,"<="&FECHA(2016,3,31))

donde podemos ver que concatenamos el operador ">=" con el numeral de la fecha usando el operador &.

La función CELDA de Excel

En un post anterior sobre funciones Excel raramente usadas tendría que haber mencionado a la función CELDA(). Por ejemplo, a lo largo y a lo ancho de los 670 posts de esta blog, hay solamente dos menciones a esta función.

Tal como lo insinúa su nombre, esta función da como resultado información sobre una celda y su entorno. La función CELDA() acepta dos argumentos, el tipo de información, que es obligatorio y la celda de referencia, que es opcional.

El uso más obvio de esta función es en fórmulas donde el resultado depende de cierta característica del contenido de la celda o del entorno donde se halla.

Esta función tiene dos características importantes:

1. es volátil, es decir que es recalculada con cualquier cambio en la hoja. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones. 
2. Si se omite la referencia a una celda la información especificada en el argumento tipo_de_info el resultado muestra la información de la última celda cambiada

Veamos algunos usos posibles.

Determinar si la celda contiene una fecha.

Excel no tiene una función nativa para determinar si el valor de una celda es una fecha (existe la función ESNUMERO pero no la función ESFECHA). Podemos usar la opción "Formato" para evaluar si el contenido es una fecha en lugar de usar la función IsDate de Vba (lo que implica crear una UDF).

Sucede que si la celda contiene una fecha la función CELDA con la opción Formato da como resultado "Dx", donde "x" es un número que identifica el tipo de formato

En este ejemplo, la fórmula =IZQUIERDA(CELDA("formato",B5),1)="D" nos permite determinar que la celda B5 contiene una fecha

SUBTOTALES con columnas ocultas 

Podemos hacerlo usando la opción "ancho" que da el ancho de la columna de referencia como número entero (SUBTOTALES realiza cálculos sin tomar en cuenta filas ocultas, pero no funciona con columnas ocultas). 

Si la columna esta oculta su ancho es 0, dato que podemor usar en conjunto con SUMAPRODUCTO tal como muestra Ismael Romero en esta nota de su blog Excelforo

El rango C1:H1 contiene la fórmula =CELDA("ancho",C4)<>0; la celda I4 contiene la fórmula =SUMAPRODUCTO(C1:H1*C4:H4).

Al multiplicar las matriz C1:H1 por la matriz C4:H4, SUMAPRODUCTO convierte los valores VERDADERO en 1 y los FALSO en 0.

Hay que tomar en cuenta que ocultar o mostrar filas no dispara el mecanismo de cálculo de Excel y por lo tanto hay que apretar F9 para actualizar el resultado de la fórmula.

Extaer el nombre de la hoja: 

Lo hacemos usando la opción "nombrearchivo" que da la  ruta de acceso completa de la celda referencia, en forma de texto.  

Supongamos que tenemos un cuaderno con una hoja por mes; si el nombre de la hoja es "agosto", esta fórmula dará como resultado "agosto"

=EXTRAE(CELDA("nombrearchivo",A1),ENCONTRAR("]",CELDA("nombrearchivo",A1))+1,256)

En esta fórmula es obligatorio usar la referencia a una celda de la hoja ya que de lo contrario elresultado será el nombre de la hoja donde se produjo el último cambio.

No, Excel no puede ayudarte a ganar la lotería

A lo largo de la vida de este blog he recibido no pocas consultas relacionadas con todo tipo de cálculos para acertar los números de la lotería. Digamoslo claramente: no, no se puede predecir el resultado de los sorteos de la lotería.
No sólo no se pueden predecir los resultados, sino que también las posibilidades ganar el primer premio son prácticamente inexistentes. Supongamos, por ejemplo, una loteria donde hay que acertar 6 de un conjunto de 49 números. Para calcular la probabilildad de acertar la cambinación ganadora, si n es el conjunto de 49 números que participan en el sorteo y k es cada una de las combinaciones posibles de 6 números, sin repeticiones, la expresión

n!
-----------------
k! * (n-k)!

donde el símbolo ! indica la operación factorial. Excel nos permite realizar el cálculo con facilidad usando la función COMBINAT(). La expresión =1/COMBINAT(n,k) nos da la probabilidad de que una combinación dada de seis números salga en el sorteo

Como puede apreciarse hay casi 14 millones de combinaciones posibles, es decir una probabilidad de 0.00000007 que nuestros números formen la combinación ganadora.

Sin embargo cada tanto alguién gana y se convierte enn un nuevo millonario. Por lo que seguiremos jugando y soñando qué hacer cuando la diosa fortuna golpee a nuestra puerta.
Y si bien Excel no nos puede ayudar a ganar la lotería, si puede ayudarnos a controlar si hemos obtenido algún premio.
Supongamos que nuestra lotería premia combinaciones de por lo menos 3 números ganadores. Supongamos también que siempre apostamos a la misma combinación y supongamos también que hemos jugado esta combinación durante diez sorteos y ahora queremos comprobar si hemos tenido suerte. Para hacerlo usaremos Formato Condicional y también SUMAPRODUCTO. La hoja Excel que aparece aquí abajo es interactiva y hay que desplazar la hoja hacia la derecha para ver las columnas K y L (también puede descargarse el archivo usando el icono en el ángulo inferior derecho).



Para señalar con un color de fondo los números sorteados que coinciden con "Mis_números" usamos format condicional con la opción Fórmula

Usamos ESNUMERO combinada con COINCIDIR para obtener resultado VERDADERO si el número evaluado coincide con alguno de los números del rango "Mis_numeros" lo hace que se aplique el formato.

En la columna L contamos la cantidad de aciertos logrados en cada sorteo con la fórmula

=SUMAPRODUCTO(--(E3:J3=Mis_numeros))

Usamos el doble guión para que Excel evalue los valores VERDADERO como 1 y los FALSO como 0.
La regla del formato condicional en la columna L es

Vencimientos en día hábil posterior

En la nota anterior vimos como en el caso que una fecha de vencimiento caiga en día no laboral (feriado o fin de semana) calcular el día hábil inmediato anterior. Por suspuesto no tanscurrió mucho tiempo hasta que un lector me consultó cómo hacer el cálculo al día hábil inmediato posterior. Por ejemplo, si la fecha de vencimiento cae un domingo, calcular la fecha del lunes siguiente.
Ambos casos serían muy sencillos de calcular si las funciones DIA.LAB o  DIA.LAB.INT contaran con la opción "0" (los siete días de la semana son laborales).
Como esa opción no existe, combinamos las funciones MAX, FILA y DIASEM en esta fórmula matricial

=MAX((C5+C4+1-FILA(INDIRECTO("1:"&C4)))*(ESERROR(COINCIDIR(C5+C4+1-FILA(INDIRECTO("1:"&C4));Feriados;0)))*(DIASEM(C5+C4+1-FILA(INDIRECTO("1:"&C4));2)<6))

donde C4 contiene la cantidad de días corridos al vencimiento y C5 la fecha inicial

El funcionamiento de la fórmula está explicado en la primer nota de la serie. Básicamente, creamos un vector de fechas partiendo de la fecha de vencimiento hacia atrás, donde las fechas de los fines de semana o de feriados reciben el valor 0; luego MAX extrae el valor mayor, que es el primer día hábil "hacia atrás".

Volviendo a la consulta de nuestro lector, queremos encontrar el primer día hábil posterior al vencimiento si este cae en día no laboral. Para encontrar la fecha vamos a aplicar una lógica similar: creamos un vector de fechas "hacia adelante" (los días posteriores a la fecha de vencimiento antes de las correcciones); las fechas que caen en días no laborales (fin de semana o feriado) reciben el valor cero. Ahora tenemos que encontrar la fecha hábil más cercana, es decir la menor, lo que podemos hacer con la función MIN (recordemos que en Excel las fechas son miembros de un serie de números enteros). Pero aquí nos encontramos con el problema que el vector de fechas incluye ceros.

Para encontrar el menor de los valores positivos de una serie (en nuestro caso, la primer fecha hábil posterior al vencimiento) excluyendo los ceros usaremos la función K.ESIMO.MENOR combinada con SUMAPRODUCTO en esta fórmula matricial

=K.ESIMO.MENOR(($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6),SUMAPRODUCTO(--((($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6))=0))+1)

Para ahorrar a mis estimados lectores los mareos (y talvez ligeras náuseas) que pueda causar la lectura de semejante fórmula, vamos a explicarla. Podemos dividir esta fórmula en dos partes

La expresión

$C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6

crea un vector de fechas donde el primer miembro es la fecha de vencimiento (fecha inicial + más diás corridos al vencimiento) y los restantes son los números de serie de las fechas siguientes o cero si la fecha cae en día no laboral. "Feriados" es un nombre definido que se refiere al rango que contiene las fechas de los feriados.
Para facilitar la lectura podemos definir un nombre que se refiere a esta fórmula (vector_fechas)

La expresión

SUMAPRODUCTO(--((($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6))=0))+1

calcula cuantos ceros hay en el vector creado por la expresión anterior que hemos encapsulado en el nombre "vector_fechas".

Si usamos el nombre "vector_fechas" en nuestra fórmula, obtenemos una versión mucho más fácil de leer y entender:

=K.ESIMO.MENOR(vector_fechas,SUMAPRODUCTO(--(vector_fechas=0))+1)

Otra ventaja de esta sintaxis es que no hay que ingresar la fórmula en forma matricial.

Veamos este ejemplo

Le fecha de vencimiento, antes de las correcciones necesarias, cae el 15/03/2015 que es un domingo; en la tabla de feriados vemos que el lunes y martes siguientes son feriados, de maner que el resultado de la fórmula es el miércoles 18/03/2015.

También podemos usar esta variante:

=K.ESIMO.MENOR(vector_fechas,INDICE(FRECUENCIA(vector_fechas,0),1)+1)

donde el número de ceros en el vector es calculado con la función FRECUENCIA. También en este caso no hace falta ingresar la fórmula en forma matricial.

Una tercera variante es usar la función MIN combinada con SI, en esta fórmula matricial

=MIN(SI(vector_fechas=0,"",vector_fechas))

El cuaderno con el ejemplo puede descargarse aquí.

Calcular días por períodos

Supongamos que queremos calcular el interés a cobrar por una deuda (por ejemplo, un pago atrasado). Durante el lapso transcurrido hay períodos con distintas tasas de interés.
El problema consiste en calcular cuantos días del lapso de la deuda caen en cada período de interés.
Consideremos esta ejemplo (el cuaderno es interactivo y puede descargarse)





El lapso de la deuda corre del 15/02/2014 al 27/06/2014, 133 días. Durante este lapso hay tres períodos de interes, tal como aparece en el rango B6:E8.

Nuestra tarea es asignar los 133 días a los distintos períodos de interés, tal como aparece en el rango F6:F8. La celda F6 contiene la fórmula

=SUMAPRODUCTO((((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))>=$C$3)*(((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))<=$D$3))

En esta fórmula usamos la técnica que ya mostré en la nota "Calcular días por años entre dos fechas". El funcionamiento es el siguiente:

• la expresión (C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1)) al estar dentro de la función SUMAPRODUCTO crea un vector de fechas , en nuestro caso {01/01/2014,02/01/2014,03/01/2014...,31/03/2014}

• al comparar los miembros de este vector con la expresión >=$C$3 creamos un vector con los valores VERDADERO o FALSO (según se cumpla la condición o no)

• la segunda expresión ((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))<=$D$3) funciona de la misma manera para la fecha del fin del lapso

• SUMAPRODUCTO multipllica ambos vectores resultando 1 cuando se multiplican dos valores VERDADERO y 0 para los restantes casos. La suma interna del vector da como resultado el número de días comprendido dentro del período de interés.

Funciones que dan #VALOR en referencias a cuadernos cerrados

Cada tanto recibo consultas en relación a fórmulas que dan el resultado #VALOR cuando contienen referencias a cuadernos cerrados.
Lo que intriga a estos usuarios de Excel es que este comportamiento no se da con todas las funciones. Ciertas funciones, como BUSCARV funcionan correctamente también si el cuaderno remoto está cerrado; otras, como CONTAR.SI dan #VALOR si el cuaderno remoto está cerrado, error que se corrige al abrir el cuaderno.

Veamos este ejemplo: tenemos dos cuadernos con las ventas anuales de dos sucursales resumidas por mes, Sucursal 1.xlsx y Sucursal 2.xlsx. En un tercer cuaderno queremos crear un reporte que muestre el total de ventas de cada sucursal  y el número de meses en que las ventas fueron superiores a los 200,000

Como puede apreciarse, la fórmula en la celda C3 contiene una referencia explícita al cuaderno de donde extraemos los datos

=CONTAR.SI('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13,">200000")

Al cerrar los cuadernos Sucursal 1.xlsx y Sucursal 2.xlsx la referencia incluye la dirección completa del archivo remoto.

Ahora cerramos el cuaderno con el reporte. Al volver a abrirlo veremos:

Como vemos, la función SUMA sigue mostrando los resultados, pero la función CONTAR.SI no puede resolver la referencia y da el resultado de error #¡VALOR!

Hay varios remedios para este problema. En el caso específico de la función CONTAR.SI podemos usar en su lugar una combinación de SUMAR y SI de esta manera:

=SUMA(SI('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13>200000,1,0))

Esta fórmula es matricial y debe ser introducida en la celda apretando simultáneamente Ctrl-Mayúsculas-Enter

También podemos usar la función SUMAPRODUCTO, que no debe ser introducida matricialmente, de esta manera

=SUMAPRODUCTO(--('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13>200000))

Nótese el doble símbolo "--"inmediatamente después del primer paréntesis. Su función es forzar a los valores VERDADERO y FALSO creados por la función SUMAPRODUCTO a tomar el valor 1 y 0 respectivamente.

Otras funciones que dan #¡VALOR! cuando se refieren a cuadernos cerrados son SUMAR.SI, SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO, CONTAR.BLANCO, PROMEDIO.SI, INDIRECTO (tema que trate en esta nota del año 2006), DESREF y varias de las funciones base de datos como BDCONTARA y BDPROMEDIO).

Promedio ponderado en tablas con filas ocultas

Ya hemos tocado el tema de cómo calcular el promedio ponderado con Excel, si bien en la prehistoria de este blog, allá por el año 2006.

A diferencia del promedio normal, el promedio ponderado toma en cuenta el "peso" relativo de cada uno de los datos en relación al resto de los datos.

Por ejemplo, en este caso

El precio promedio, sin tomar en cuenta las cantidades vendidas por cada sucursal, es 15.50. Pero si tomamos en cuenta el "peso" de cada sucursal, vemos que en la mayor parte de las ventas el precio está por debajo del promedio, por lo cual el promedio ponderado noos da 12.84.
El promedio ponderado lo calculamos con la fórmula

=SUMAPRODUCTO(C3:C6,D3:D6)/SUMA(C3:C6)

Alternativamente podríamos agregar totales a la tabla y calcular el promedio ponderado dividiendo el total de venta (cantidad x precio) por el total de piezas vendidas

Ahora vamos a complicar las cosas y ver cómo podemos calcular el promedio ponderado en tablas filtradas (tablas en las que hemos aplicado Autofiltro u ocultado filas).

Veamos este ejemplo

El promedio sencillo del precio lo podemos calcular con SUBTOTALES, que ignora las celdas ocultas

Para calcular el promedio ponderado se nos presenta el problema el problema que, a diferencia de la función SUBTOTALES, SUMPRODUCTO toma en cuenta también las celdas ocultas en el rango.

Una forma de calcular el promedio ponderado en listas filtradas es usar columnas auxiliares (en este caso una única columna  auxiliar), usando la función SUBTOTALES con la opción 103 (CONTARA)

Al referirse a una única celda la función dará un valor 0 si la celda está en una fila oculta y 1 si la fila es visible. Luego podemos usar esta columna para calcular el promedio ponderado usando SUMPRODUCTO

=SUMAPRODUCTO(D4:D15*E4:E15*F4:F15)/SUMAPRODUCTO(D4:D15*F4:F15)

Si queremos evitar el uso de columnas auxiliares podemos usar esta fórmula que combina SUMAPRODUCTO con SUBTOTALES y DESREF

=SUMAPRODUCTO(SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1)),D4:D15,E4:E15)/SUBTOTALES(9,D4:D15)

La expresión

SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1))

da un vector de 1 (fila visible) o 0 (fila oculta). Para comprobarlo podemos seleccionar la expresión en la barra de las fórmulas y apretar F9 para ver el resultado

Esta fórmula de autoría incierta (posiblemente Bob Philips), aparece en varios foros de Excel.

Calcular días por años entre dos fechas

Supongamos una lista de fechas de inicio y finalización de tareas (o contratos o cualquier otra actividad) que se extienden a más de una año, como en este ejemplo:

Podemos ver, por ejemplo, que la Tarea 1 empieza en el 2013 y termina en el 2015. ¿Cómo hacemos para calcular cuántos días caen en cada año?

Para resolver este problema vamos a usar una técnica no tan conocida: crear dentro de la fórmula un vector implícito (o "virtual"). El concepto quedará más claro cuando expliquemos la fórmula.

La columna "Total de días" muestra el total entre ambas fechas (incluida el día del comienzo) usando la fórmula =SIFECHA(C4,D4,"d")+1

Para poder calcular cuantos días caen dentro de cada año (2013,2014,2015), vamos a agregar tres columnas en nuestra tablam una para cada año, y en la celda F4 ponemos esta fórmula (que luego copiamos al resto de la tabla):

=SUMAPRODUCTO(--(AÑO($C4+FILA(INDIRECTO("1:"&($D4-$C4+1))))=F$3))

Vamos a "disecar" la fórmula de adentro hacia afuera:

• La expresión "($D4-$C4+1)" calcula el total de días transcurridos entre el principio y el fin de la tarea. Es equivalente al cálculo en la celda E4.
• La expresión INDIRECTO("1:"&($D4-$C4+1)) crea una referencia al rango "1:750". Al usar esta expresión como argumento de la función FILA en forma matricial (al estar dentro de la función SUMAPRODUCTO), obtenemos un vector con los valores 1,2,3,...,750
• Al sumar a $C4 este vector en ($C4+FILA(INDIRECTO("1:"&($D4-$C4+1))), obtenemos un vector de fechas día por día que comienza el 06/07/2013 y termina con el 25/07/2015
• La función AÑO extrae de cada fecha el año correspondiente, por lo que obtenemos un vector de años con 750 puntos.
• Este vector los comparamos con el año que aparece en la celda F$3, por lo que obtenemos un vector de valores VERDADERO o FALSO (si se cumple o no la condición).
• La doble negación a la izquierda de la función AÑO convierta los valores VERDADERO en 1 y los FALSO en 0. Igualmente podríamos haber multiplicado la expresión por 1.
• Finalmente SUMAPRODUCTO suma todos los valores del vector que cumplen con la condición. Recordemos, como puse más arriba, que SUMAPRODUCTO se comporta en forma matricial.

Podemos visualizar el proceso del cálculo usando la herramienta "Evaluar fórmula" en "Fórmulas-Auditoría de fórmulas"

En este video comento el proceso

Calcular el promedio de los últimos n elementos de una serie.

Un lector me envía esta consulta:

En la columna A tengo una lista larga de nombres (por ej A, B, A, A , B, A; etc); en la columna B tengo un puntaje asociado a cada nombre.
Necesito calcular el promedio de los últimos 4 (o podría ser 5 o 6) puntajes correspondientes al nombre respectivo.

Supongamos que esta es nuestra lista

Como queremos calcular usando los últimos 4 (o cualquier otro número) miembros de la lista, por nombre, vamos a crear una columna auxiliar con el número de orden para cada valor de cada nombre. Esto lo haremos con la función CONTAR.SI

En la fórmula =CONTAR.SI($A$3:A3,A3) la primera referencia del rango de búsqueda ($A$3) es absoluta. De esta manera la referencia se va "expandiendo" a medida que copiamos la fórmula a los largo de la columna

Una vez agregada la columna auxiliar podemos calcular el promedio de los últimos n valores de cada nombre usando esta fórmula

=SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)*Valor)/SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1))

Personalmente no me gustan esta "super fórmulas", por lo que prefiero dividirlas en dos (o más) fórmulas intermedias

La fórmula en la celda G5 (Suma) es

=SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)*Valor)
La fórmula en G6 (Recuento) es

=SUMAPRODUCTO((Nombre=G2)*(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1))
Y la obvia fórmula en G7 (Promedio) es =G5/G6

Antes de analizar cómo funcionan las fórmulas aclaremos que hemos asignado los rangos de valores a nombres definidos

¿Cómo funciona la fórmula en G5?

Primero debemos calcular cuál en el número de orden mayor para el nombre en la lista (el último, por ejemplo para B será 13), lo que hacemos con

MAX((Nombre=G2)*(Nro._Orden))

Como esta fórmula está incluida dentro de SUMAPRODUCTO, Excel la calculará en forma matricial. Si queremos usarla independientemente tendremos que introducirla apretando simultáneamente Ctrl-Mayúscula-Enter.

Para que Excel busque los últimos 4 valores usamos

(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)

que en el caso de buscar el resultado para B, da 10. Esto podemos comprobarlo seleccionando la parte de la fórmula y apretando F9 (luego apretamos ESC para deshacer el cálculo)

En el próximo paso

(Nro._Orden>=MAX((Nombre=G2)*(Nro._Orden))-G3+1)

SUMAPRODUCTO asigna el valor VERDADERO a todos los valores de número de orden que sean mayores o iguales a 10. Siendo 13 el mayor, habrá 4 VERDADEROS en el vector.

La expresión (Nombre=G2) dentro de SUMAPRODUCTO crea una vector con valores VERDADERO para las fila del nombre buscado y FALSO para el resto.

La multiplicación de ambos vectores crea un vector de valores VERDADERO sólo para las filas en que ambas condiciones se cumplan.

Finalmente al multiplicar este vector por el de los valores, obtenemos la suma de las filas que cumplen ambas condiciones simultáneamente.

La fórmula para el Recuento hace lo mismo sin multiplicar por los valores.

Un último detalle es el formato numérico personalizado en la celda G3

El archivo puede descargarse aquí

Cuando CONTAR.SI da resultados erróneos

Si necesitamos contar condicionalmente, nuestra primera elección es la función CONTAR.SI. Pero en ciertas situaciones, CONTAR.SI puede dar resultados equivocados.

Veamos este ejemplo “basado en un hecho real” (uno de mis clientes). Supongamos una empresa que usa códigos alfanuméricos para identificar sus productos. Con este sistema, 12345 y 012345 son dos productos distintos. Ahora queremos contar cuántas veces se repite un código en una lista, por ejemplo en esta

Fácil, ¿no?
A simple viste vemos que 12345 se repite tres veces y 012345 se repite dos veces. Veamos que pasa con CONTAR.SI

Usamos =CONTAR.SI($A$2:$A$6,C2) en D2 y =CONTAR.SI($A$2:$A$6,C3) en D3 y en ambos casos el resultado es 5!

Esto se debe a que CONTAR.SI evalúa todo valor como número, de manera que 012345 es evaluado como número y por lo tanto es considerado idéntico a 12345.

La solución es usar SUMAPRODUCTO, de esta manera

=SUMAPRODUCTO(--($A$2:$A$6=C2))

SUMAPRODUCTO genera un vector de con los valores de la celda del rango

y los compara con el valor de la condición, generando un vector de valores VERDDERO t FALSO

Finalmente usamos el doble “--“ para convertir los VERDADERO en 1 y los FALSO en 0.

Quien no se sienta cómodo con el uso del doble “-“ puede multiplicar el vector por 1

=SUMAPRODUCTO(($A$2:$A$6=C2)*1)

La función SUBTOTALES con criterios.

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).