Realizar sumas condicionales con la formula SUMAPRODUCTO (SUMPRODUCT)

En una nota anterior he comentado como realizar sumas con más de una condición utilizando la formula SUMAR.SI.CONJUNTO, pero esta fórmula solo está disponible en Excel 2007 en este artículo explicaré una alternativa que sirve para Excel 2003 o anteriores.

La fórmula SUMAPRODUCTO realiza la multiplicación entre los arrays enviados como parámetros y luego suma cada uno de los resultados de esas multiplicaciones.

Se pueden incluir desde 2 hasta 255 matrices en esta formula y las matrices pueden ser provistas mediante rango de celdas o rangos con nombre o arrays constantes. La fórmula devolverá el error #VALOR! si los arrays tienen distintas longitudes.


Veamos algunos ejemplos


Si tenemos una lista de productos con su precio unitario y la cantidad vendida podemos en una sola celda calcular el valor total sin necesidad de utilizar una fórmula en cada línea para calcular el valor total de cada línea.


Utilizando la siguiente formula podemos calcular el total =SUMAPRODUCTO(B2:B6;C2:C6)

Otra aplicación de esta fórmula es para realizar sumas condicionales, la idea es que en el primer parámetro ingresemos rango que queremos sumar y luego las condiciones que queremos cumplir para sumar los números.


Utilizaremos la formula =SUMPRODUCTO(C2:C14;--(B2:B14="Manzana");--(A2:A14="Argentina"))

Las condiciones devolverán arrays con valores "VERDADERO" o "FALSO" que luego convertiremos a 1s y 0s. Si el valor es verdadero usaremos 1 y si es Falso usaremos 0. Al utilizar este proceso multiplicaremos cada elemento del primer parámetro por 1s o 0s dependiendo de la condición.

El -- es utilizado para convertir los valores "VERDADERO" o "FALSO" en 1s o 0s. Solo aquellos elementos que sean multiplicados por 1 llegaran a tener un valor para el paso final de la suma.

Si no incluimos el rango a sumar y solamente las condiciones estaremos realizando una fórmula similar a CONTAR.SI.CONJUNTO

Para bajar el archivo de ejemplo pueden hacer clic aqui.

Buscar el valor más frecuente en una lista con la función MODA

Cuando necesitamos encontrar el valor que se repite con más frecuencia en una lista o rango de celdas podemos utilizar la función MODA (o MODE para la versión en inglés).

La función solo toma un parámetro que es el rango donde se encuentran los valores a evaluar. Como resultado se devuelve el valor que mas aparece en la lista.

La principal limitación de esta función es que solo funciona con valores numéricos y muchas veces en Excel necesitamos buscar el valor más frecuente sobre un campo de texto. Por ejemplo supongamos que disponemos de una lista de artículos vendidos a clientes y queremos saber quien fue el cliente que ha realizado mas pedidos, dado que el nombre de cliente no es un número no podríamos usar dicha función.

Para evitar este problema podemos usar una combinación de formulas que nos permitirá utilizar la función MODA sobre un rango de valores que no sean numéricos.


Veamos el ejemplo


Utilizaremos la siguiente fórmula para buscar el valor más frecuente

{=INDICE(B2:B10;MODA(COINCIDIR(B2:B10;B2:B10;0)))}

La idea es que utilicemos algún valor numérico que identifique unívocamente al elemento. En este caso utilizamos la posición donde aparece por primera vez el elemento dado que es un valor numérico. Luego con la función índice devolvemos el elemento ubicado en esa posición.

Para ubicar la posición que ocupa cada uno de los elementos en la lista utilizamos la función COINCIDIR. Recuerden que en el caso de estar repetido un valor siempre se devolverá la primera ubicación.


Con el array de posiciones devuelto por COINCIDIR utilizamos la función MODA que devuelve aquel número que más se repite.


Finalmente con la función INDICE devolvemos el nombre que se encuentra en esa posición.

Combinando una serie de formulas que vimos en artículos anteriores podemos ampliar o resolver algunos problemas que no son posibles con la utilización de una sola formula

Para ver el archivo del ejemplo pueden hacer clic aqui.

Evaluar formulas en Excel

Cuando tenemos formulas complejas y no funcionan como necesitamos es difícil encontrar el problema. Para ayudarnos con esta tarea Excel posee una herramienta Evaluar Fórmulas que nos permite seguir paso a paso cada uno de los cálculos que Excel realiza hasta llegar al valor final.

Esta herramienta es de especial importancia cuando tenemos funciones con matrices o arrays ya que en éstas Excel realiza muchos cálculos que están incluidos en una sola fórmula.

En Excel 2007 la herramienta se encuentra en la ficha fórmulas dentro del grupo Auditoría de fórmulas


En Excel 2003 la herramienta se encuentra en el menú Herramientas, auditoría de fórmulas, Evaluar fórmulas.

Una vez seleccionada la herramienta podemos evaluar la fórmula paso a paso haciendo clic en el botón Evaluar.


Veamos un ejemplo


Utilizaremos la fórmula publicada en el articulo "Utilizando Matrices o Arrays en Excel" para sumar los 3 valores más grandes de una lista :

{=SUMA(K.ESIMO.MAYOR(A1:A10;FILA(INDIRECTO("1:3")))}

En el primer paso Excel resuelve la formula INDIRECTO("1:3") lo que nos devuelve la referencia para la siguiente fórmula


En el segundo paso Excel resuelve FILA("$1:$3") al ser una fórmula array esta se evalúa tres veces y su resultado es un array {1,2,3}


En el tercer paso Excel resuelve la fórmula K.ESIMO.MAYOR(A1:A10;{1,2,3}), nuevamente al tratarse de una formula array Excel la evalúa tres veces devolviendo el mayor, el segundo mayor y el tercer mayor en un nuevo array


En el último paso Excel resuelve la fórmula SUMA, sumando todos los valores devueltos en el array del paso anterior.

Utilizando matrices o arrays en Excel

Un array puede definirse como una colección de elementos. En Excel podríamos decir que un rango de celdas es un array pero también podemos crear nuestros propios arrays o funciones de arrays. Esto último podría definirse como una colección de elementos a los que les aplicamos una formula.

Las funciones de array las podemos utilizar para realizar cálculos complejos que de otra forma requieren ingresar varias formulas en varias celdas. De esta forma podemos reducir el ingreso de formulas como asi también los posibles errores.

Para ingresar una formula array debemos presiones Ctrl + Shift + Enter y no solo la tecla enter. Excel agregará { } en la formula indicando que es una formula array. Si no se presiona Ctrl + Shift + Enter Excel la considerara una formula normal y por lo tanto no funcionará como deseamos.

Si bien las formulas array son muy utiles no son muy utilizadas y una razón es que son mas difcilis de entender ya que una sola celda se realizan varios cálculos y cuando no funciona correctamente es mas difícil seguir los resultados.

Array constantes

En Excel podemos definir un array que esta compuestos de valores constantes y el mismo puede ingresarse en una celda o se puede asignarse a un nombre de la misma forma que lo hacemos para rangos de excel.

Por ejemplo podemos definir un nombre llamado Dias e ingresar : ={"Lunes";"Martes";"Miercoles";"Jueves";"Viernes"}.
Para los arrays constantes es necesario ingresar { }.

Para ingresar el array constante debemos ir a la ficha Formulas y presionar en el botón Definir Nombres



Para obtener un valor particular del array de constantes podemos utilizar la función INDICE de la siguiente forma : =INDICE(Dias, 1) para devolver el primer elemento. El segundo parámetro puede cambiarse para devolver el valor deseado.


Veamos algunos ejemplos de funciones con arrays

Sumar los diez numeros mas grandes de un rango
{=SUMA(K.ESIMO.MAYOR(A1:A100,{1;2;3;4;5;6;7;8;9;10))}

Si tenemos que sumar gran cantidad de numeros podemos usar esta alternativa :

{=SUMA(K.ESIMO.MAYOR(A1:A100,FILA(INDIRECTO("1:10")))}

Para mas información sobre como Excel resuelve esta fórmula puedes hacer clic aqui.

Sumar con condiciones
{=SUMA(SI(A1:A10>0;A1:A10))}

Si bien este resultado puede obtenerse utilizando la función SUMAR.SI con el array podemos definir condiciones mas complejas e incluso varias condiciones.

El tema de los arrays es muy extenso por lo tanto en as próximas semana agregaré otra nota con ejemplos.

Conteo de elementos con varios criterios CONTAR.SI.CONJUNTO

En el artículo de hoy explicaré algunos ejemplos sobre como utilizar la función CONTAR.SI.CONJUNTO (CONUNTIFS para la versión en inglés) que funciona de forma similar a SUMAR.SI.CONJUNTO (explicada en un articulo anterior).

La función CONTAR.SI.CONJUNTO permite contar la cantidad de registros que cumplen con un criterio definido por el usuario. Esta función permite definir mas de una condición a diferencia de la función CONTAR.SI.

Se deben proporcionar parámetros de a pares y los primeros dos se utilizan de la siguiente forma. El primero es el rango donde se desea aplicar el criterio y el segundo es la condición que se desea cumplir. Esta función acepta un máximo 127 criterios diferentes con lo cual se pueden definir criterios muy específicos.

Si bien se pueden definir varios parámetros de criterio estos deben tener la misma cantidad de celdas para poder ser utilizados en una única función.


Veamos algunos ejemplos


Disponemos de una lista de productos que fueron comprados por un grupo de clientes durante los meses de enero a marzo.


Si deseamos saber la cantidad de ventas que se efectuaron al cliente Gustavo utilizamos la formula =CONTAR.SI(B2:B100;"=Gustavo")

Si necesitamos solo contar las ventas que se efectuaron entre el 01/01/2009 y el 15/01/2009 para el cliente Gustavo utilizamos la formula : =CONTAR.SI.CONJUNTO(B2:B100;"=Gustavo";A2:A100;">=01/01/2009";A2:A100;"<=15/01/2009")

Para versiones anteriores a Excel 2007 podemos utilizar una formula matricial que nos permite obtener el mismo resultado : {=CONTAR(SI(($B$2:$B$100="Gustavo")*($A$2:$A$100>=FECHA(2009;1;1))*($A$2:$A$100<=FECHA(2009;1;15));1))}

Recuerden que las formulas matriciales se deben ingresar presionando Ctrl + Shift + Enter. Para mas información sobre matrices o array puede leer esta nota.

Para acceder el archivo de ejemplo pueden hacer clic aqui