Las posibilidades de SUMAPRODUCTO en Excel.

Hoy voy a hablar sobre algunas de las funciones más empleadas en nuestras hojas de Excel, las funciones SUMAR.SI y SUMAR.SI.CONJUNTO (de las que se han hablado largo y tendido en este blog); pero sobre todo de una función alternativa a estas, con multitud de ventajas, la función SUMAPRODUCTO.
En muchas ocasiones al elaborar nuestros cálculos nos hemos dado cuenta de las limitaciones de esas funciones SUMAR.SI o SUMAR.SI.CONJUNTO, que aunque muy potentes, nos restringen las operaciones al criterio Y, es decir, funcionan únicamente con sumas acumulativas de registros que cumplen condiciones simultáneas. Por ejemplo:
=SUMAR.SI.CONJUNTO(A1:A10;B1:B10;>100;C1:C10;"a")
que sumaría los importes del rango A1:A10 sólo en los casos en que la cantidad de B1:B10 correspondiente sea mayor a 100 y al mismo tiempo en C1:C10 contenga el texto "a".

¿Pero, y qué podemos hacer si en lugar del criterio Y deseamos aplicar el criterio O?. Aquí es donde comienza la potencia de SUMAPRODUCTO.

Si buscamos en la ayuda de Excel esta función leeremos: SUMAPRODCUTO Multiplica los componentes correspondientes de las matrices suministradas y devuelve la suma de esos productos.; y efectivamente este sería el uso más habitual, sin embargo es mucho más interesante observar los argumentos de la función, en la Sintáxis:
SUMAPRODUCTO(matriz1; [matriz2]; [matriz3]; ...)

Lo especial de esta función es que permite trabajar u operar con Rangos o matrices!!!, lo que es poco frecuente, excepto con las funciones matriciales. Es, sobre este punto, sobre el que incidiré.
Esta forma parecida a la matricial de trabajar de nuestra función es la que ocupará nuestra entrada del día; ya que con ella podremos operar empleando ambos criterios lógicos Y y O (AND y OR); esto lo conseguiremos empleando los operadores * y +:
para el operador lógico Y usaremos *
para el operador lógico O usaremos +

En esencia conseguiremos que Excel evalúe las condiciones que le vamos a dar, empleando estos operadores, para que nos devuelva VERDADERO o FALSO (1 ó 0 en valor numérico respectivamente), todo ello sin emplear la forma matricial de ejecutar las funciones.

Veremos, como siempre, algunos ejemplos de este uso especial de SUMAPRODUCTO. Partiremos de la siguiente base de datos, al que hemos asignado ciertos Nombres definidos para trabajar de una forma más visual:
Comercial =Hoja2!$A$2:$A$14
Importe =Hoja2!$D$2:$D$14
Unidades =Hoja2!$C$2:$C$14
Zona =Hoja2!$B$2:$B$14

Un primer ejemplo serviría para sustituir a las funciones SUMAR.SI o SUMAR.SI.CONJUNTO.
=SUMAR.SI.CONJUNTO(Importe;Comercial;"Pepe";Unidades;">60")
puede ser sustituido por
=SUMAPRODUCTO(Importe*(Comercial="Pepe")*(Unidades>60))

En ambos casos nos indica cuánto suman los importes para el comercial 'Pepe' cuando las Unidades vendidas superan las 60.
Notemos como las dos condiciones de SUMAR.SI.CONJUNTO son equivalentes al operador * (que implica el criterio lógico Y).

haz clic en la imagen

El anterior ejemplo no aporta mucha funcionalidad, pero asienta las bases para el siguiente ejemplo, en el cual combinaremos un operador lógico Y(AND) con un operador lógico O(OR), empleando * y +. Fijémosnos en la siguiente función:
=SUMAPRODUCTO(Importe*((Comercial="Pepe")+(Comercial="Luís"))*(Zona="Centro"))
con ella estamos obteniendo la suma de los Importes cuando se cumpla que el Comercial sea 'Pepe' o 'Luís' y además para cualquiera de ellos la Zona de trabajo sea 'Centro'.

haz clic en la imagen

La explicación de cómo funciona esta fórmula es idéntica a cómo lo hace una matricial, evalúa las condiciones, devolviendo 1 ó 0 en caso de cunplimiento o no. Sobre nuestro ejemplo en esta última operación, el primer registro es el Comercila 'Pepe', así que Comercial="Pepe" devolvería 1 (O VERDADERO), como obviamente no es comercial 'Luís', entonce Comercial="Luís" devolverá 0 (o FALSO), asi que
((Comercial="Pepe")+(Comercial="Luís")) = (1 + 0) = 1
Igualemente con Zona="Centro", para el primer registro es cierto, así que
(Zona="Centro") = 1
Ya podemos operar todo junto
Importe*((Comercial="Pepe")+(Comercial="Luís"))*(Zona="Centro") = 10.94 * (1 + 0 ) * 1 = 10.94
que acumulará sucesivamente con el resto de registros de nuestra tabla.

Y una última posibilidad de la función SUMAPRODUCTO nos lleva a emplearla para conteos, esto es, para sustituir a las funciones CONTAR.SI o CONTAR.SI.CONJUNTO.
La forma es prácticamente idéntica a las anteriores, basta eliminar el campo de suma que empleabamos; entonces si queremos conocer el número de registros que verifican para el comercial 'Pepe' que las Unidades vendidas superan las 60:
=CONTAR.SI.CONJUNTO(Comercial;"Pepe";Unidades;">60")
puede reemplazarse por
=SUMAPRODUCTO((Comercial="Pepe")*(Unidades>60))

sobre nuestro ejemplo el resultado en ambos casos es de 2, es decir, sólo dos registros cumplen simultáneamente las dos condiciones.

De igual forma podemos saber el número de veces que se cumplen el criterio combinado para el Comercial sea 'Pepe' o 'Luís' y además para cualquiera de ellos la Zona de trabajo sea 'Centro':
=SUMAPRODUCTO(((Comercial="Pepe")+(Comercial="Luís"))*(Zona="Centro"))

Y todo esto sin emplear fórmulas matriciales, sencillamente aplicando las mismas normas de éstas, a una función estándar de Excel.