sumaproducto: otra función útil y poco intuitiva de excel

Utilicé esta función en mi anterior entrada, para multiplicar dos rangos (matrices), lo que originó inmediatamente la consulta de un amigo, dado que no estaba al tanto de la existencia de ella. Y luego de pensarlo un poco y analizar el contenido de muchos mensajes o correos electrónicos que recibo, observo que la gran mayoría del usuario promedio de Excel no la utiliza. Y existen un par de razones: hay otras formas de trabajo (prescindiendo de SUMAPRODUCTO), no existen muchos ejemplos y es una función matricial. Esto último es igual a decir: complicada, poco documentada y tabú. Intentaré brindar un par de ejemplos bien sencillos sobre esta función que nos ayudará a realizar cálculos complejos y nos servirá de "atajo" en mas de una ocasión, ya que sabiéndola utilizar evitaremos el uso de varias "funciones anidadas" o "rebuscadas".

=SUMAPRODUCTO(Matriz1; Matriz2; Matriz3....)

La sintaxis no es complicada: cada argumento de la función es una matriz o rango de datos. Cabe aclarar que todas las matrices pasadas como parámetro deberán tener el mismo tamaña, es decir, cantidad de elementos.

Veamos el principal y mas básico uso de SUMAPRODUCTO(): la suma de los productos de dos matrices.

La siguiente imagen aclarará convenientemente el panorama:

en C2 podemos observar el resultado de multiplicar cada elemento de ambas matrices y, posteriormente, la suma de todos ellos.

A mano: (2 * 20) + (3 * 30) + (4 * 40) + (5 * 50) = 40 + 90 + 160 + 250 = 540

Ya podemos observar a simple vista el trabajo que nos ahorró esta función. Es matricial (va almacenando resultados parciales), aunque no se ingresa con el "ctrol + alt + enter" ni aparecen las típicas llaves ( { } ) de toda función matricial. Si a al ejemplo anterior le agregamos un par de rangos, nos será mas que evidente el gran trabajo manual que nos evita:

y así hasta el infinito... y mas allá. estamos a estas alturas convencidos que realizar este cálculo por otra vía  hubiese sido un trabajo mucho mas engorroso.

Pero, por lo menos a mí, esto me deja "sabor a poco". Y en realidad es así, por que esta función da para mucho mas; es lo que trataré de explicar en las próximas líneas.

ex

Un trabajo realmente bueno que se puede llevar a cabo es contar o sumar con varias condiciones. Si bien Excel posee funciones específicas para esta tarea (CONTAR.SI o SUMAR.SI), carecen de un punto vital: solo aceptan una condición. Con SUMAPRODUCTO() podemos crear contadores o sumas con varias condiciones, de forma sencilla, con solo conocer un poco su lógica.

He mencionado en varias oportunidades que los valores VERDADERO y FALSO son tratados como números: VERDADERO=1 y FALSO=0. Les paso una imagen para refrescar la memoria:

si multiplico números (columna A) por Verdaderos o Falsos (columna B) obtendré el mismo resultado que multiplicar por 1 o 0 (columna C) respectivamente.

Lo anteriormente mencionado, en combinación con SUMAPRODUCTO(), será la herramienta que nos permitirá la creación de sumas y cuentas condicionales. Supongamos la siguiente tabla:

tres columnas archiconocidas y usadas: vendedor, zona e importe. muy básico, pero efectivo

Vamos a contar las ventas de Juan en la zona norte:

=SUMAPRODUCTO((A2:A12="juan")*(B2:B12="norte"))

efectivamente, Juan realizó dos ventas en la zona norte.

Para explicar mejor, diría: dentro de SUMAPRODUCTO() estoy multiplicando dos matrices: la columna de los nombres y la columna de las zonas. Como esta función es matricial, va analizando elemento x elemento. Si encuentra algún valor VERDADERO ("juan" o "norte") ese VERDADERO se convierte en 1; entonces "almacena" ese 1 y siguen con el próximo elemento. De esta forma halló solo dos registros en toda la tabla cuyos datos Vendedor y Zona son coincidentes, almacenó dos veces 1 ... y la suma de dos 1 es = 2.

Vean la fila dos de la tabla: Juan y Norte se encuentran juntos.... listo: VERDADERO.

Vean la fila diez de la tabla: Juan y Norte se encuentran junsto.....listo: VERDADERO.

O sea: dos VERDADEROS es igual a  1 + 1, que es el 2 que obtenemos como resultados final.

Calculamos hasta aquí la cantidad de ventas, en base a dos condiciones. En la celda de abajo expondremos el total de esas ventas:

=SUMAPRODUCTO((A2:A12="juan")*(B2:B12="norte")*(C2:C12))

ahá, así de fácil: sólo agregué la matriz (rango) que deberá multiplicar cada vez que "juan" y "norte" se encuentren, es decir: cuando ambos datos sean VERDADEROS, SUMAPRODUCTO() multiplicará por 1 el valor de la Columna C (importe), almacenará ese resultado, y al finalizar el recorrido de la tabla los sumará

A estas alturas se encontrarán evaluando lo poderosa y útil que es esta función. Y podemos agregar tantas condiciones como necesitemos, con la sola condición de estar planteándole a Excel algo lógico.

Otro ejemplo muy práctico: ¿que usuario de Excel no se desveló pensando en la forma de sumar las ventas correspondientes a un mes determinado? Y luego de mucho batallar llegamos a diseñar complejas funciones anidadas, haciendo también uso de celdas o columnas auxiliares... bueno, se terminó la historia. Con SUMAPRODUCTO() es sencillo lograrlo:

=SUMAPRODUCTO((MES(A2:A7)=1)*(B2:B7))

exacto, el total de ventas de enero asciende a 30.

Explicación: en la primer parte de la función (MES(A2:A7)=1) le digo a Excel que vaya analizando si el MES de las celdas del rango A2:A7 es  igual a 1. Y en la segunda parte (B2:B7) le estoy indicando los valores que deberá multiplicar por 1 (verdadero) cuando la anterior condición se cumpla.

En la imagen que sigue les aclaro la lógica:

no caben dudas que SUMAPRODUCTO() nos ahorra mucho trabajo

Es mas, podemos ir un poco mas lejos: de ser necesario, se nos hace factible sumar por trimestres. Aqui la cuestión se empieza a complicar un poco, dado que anidaremos otras funciones dentro de SUMAPRODUCTO()... pero a no tenerle miedo, la lógica es la misma:
=SUMAPRODUCTO((MES(A2:A11)>=1)*(MES(A2:A11)<=3)*(B2:B11))

Aclarando:
primer parte: devolverá VERDADERO solo si el mes de la celda es mayor o igual a 1 (enero)
segunda parte: devolverá VERDADERO solo si el mes de la celda es menor o igual a 3 (marzo)
tercera parte: suma todas las coincidencias encontradas, cuyos meses estén comprendidos entre enero y marzo, es decir, el primer trimestre del año.

Y hay mas, pero lo dejaré para futuras entradas, en donde seguiremos viendo la forma de ahorrar y encarar trabajos complejos mediante esta increíble y poco conocida función. No sigo ahora por dos motivos: permitir que practiquen con lo visto hasta el momento y no hacer tan larga la entrada.
Les dejo el link a la descarga del archivo.