SUMAPRODUCTO, la función multiusos

En la definición oficial de Microsoft se puede ver que la función SUMAPRODUCTO multiplica cada uno de los elementos de una o varias matrices y devuelve la suma de los productos.

Efectivamente, esto es lo que hace. Pero lo que no dice en esta página es que no sólo sirve para eso. Se trata de una de las funciones más versátiles con que cuenta Excel. Es capaz de manejar matrices de una forma muy elegante. Por eso, SUMAPRODUCTO es realmente útil (o en mi opinión, imprescindible) a la hora de comparar dos o más rangos y calcular datos con varios criterios.

En este artículo te muestro cómo funciona SUMAPRODUCTO, desde el uso más simple (el que aparece en la web de Microsoft) hasta los más complejos con los que podrás comparar matrices, contar y sumar celdas con varios criterios, contar valores únicos y duplicados, extraer números de cadenas alfanuméricas, contar los caracteres de un rango de celdas… ¿quieres que siga diciendo utilidades?

Artículos relacionados

• SUMAPRODUCTO: Sintaxis y ejemplos
• Ejemplos avanzados con SUMAPRODUCTO

Definición y sintaxis de SUMAPRODUCTO

Como has leído más arriba, SUMAPRODUCTO es capaz de multiplicar los elementos de una o varias matrices (hasta 255), y devolver la suma de todos los productos.

La sintaxis no es muy complicada:

=SUMAPRODUCTO(rango_1;[rango_2;…])

• Rango_1: Obligatorio. Es el primer rango cuyos elementos quieres multiplicar y sumar.
• Rango_2, Rango_3, …: Opcional. Es el segundo, tercero, …n rango de celdas hasta un máximo de 256. En versiones de Excel anteriores a 2007, este número se reducía a 30 rangos.
• El número mínimo de rangos que se pueden incluir es uno, de forma que la función dará como resultado la suma de los elementos de la matriz.

Aunque SUMAPRODUCTO funciona con matrices, no es necesario introducirla pulsando Ctrl + Mayús + Intro (como la mayoría de fórmulas matriciales). Basta con pulsar Intro como con cualquier otra fórmula.

Algunas cosas que debes tener en cuenta:

• Todas las matrices introducidas como argumentos deben tener el mismo número de filas y de columnas. Si no es así, el resultado de la función será el error #¡VALOR!
• Si uno de los argumentos contiene valores no numéricos, éstos serán tratados como ceros.
• Si una de las matrices es un valor lógico, el resultado será VERDADERO o FALSO. En caso de tener que realizar algún calculo con este resultado, necesitarás convertir los valores a sus correspondientes numéricos (1 o 0). Puedes hacer esto utilizando el operador doble negación (–).

¿Cómo funciona SUMAPRODUCTO?

Para entender el funcionamiento de SUMAPRODUCTO te propongo un ejemplo:

Imagina una hoja en la que en el rango A2:A5 tienes una serie de cantidades y en el rango B2:B5, los precios de los productos. El objetivo sería hallar el importe de la compra.

En el colegio te enseñaron que primero debes multiplicar cada cantidad por su precio y luego sumar esos importes parciales. Pues eso mismo hace SUBTOTALES:

=SUMAPRODUCTO(A2:A5;B2:B5)

Observa la imagen:

La fórmula coge el primer número de la primera matriz y la multiplica por el primer número de la segunda matriz. A continuación coge el segundo número de la primera matriz y lo multiplica por el segundo número de la segunda matriz…

Una vez que ha terminado de multiplicar, va sumando los resultados parciales para devolver el total.

Es decir, la función realiza las siguientes operaciones matemáticas:

= A2*B2 + A3*B3 + A4*B4 + A5*B5

Es una operación básica que podrías hacer incluso manualmente…, ¡pero imagina que en vez de cuatro registros tuvieses 400!

Algunos ejemplos para operar con condiciones

Multiplicar dos o más rangos para luego sumar sus resultados es sólo la parte más básica del uso de SUMAPRODUCTO. La verdadera potencia de esta función es que va mucho más allá de este objetivo. Te muestro algunos ejemplos que he ido recopilando a lo largo del tiempo:

SUMAPRODUCTO con uno o varios criterios Y

Ya sabes que en Excel siempre hay varias formas de hacer la misma tarea. En este ejemplo te muestro cómo utilizar SUMAPRODUCTO para sustituir la función CONTAR.SI.

Imagina que tiene una lista de productos en los que se muestra la previsión anual de ventas y lo que se ha vendido de cada uno. El objetivo de este ejemplo sería el de hallar el número de productos cuya venta real ha superado a la prevista.

=SUMAPRODUCTO(–(C2:C11>B2:B11))

En la columna C se encuentran los importes reales y en la B los previstos.

¿Y qué ocurre si tenemos más de una condición? Imagina que, además de contar los productos cuyo importe real es mayor que el previsto, quieres que solo cuente el número de “Producto 2”. La solución estaría en agregar otro criterio de esta forma:

=SUMAPRODUCTO(–(C18:C27>B18:B27);–(A18:A27=”Producto 2″))

Sumas, cuentas y promedios con varias condiciones Y

Los más “antiguos” en el uso de Excel recordarán que hasta la introducción de las funciones SUMAR.SI, CONTAR.SI y PROMEDIO.SI en Excel 2007, la única forma de poder hacer operaciones de forma condicional era con SUMAPRODUCTO.

Te pongo ejemplos de cada una de las operaciones según la imagen de debajo:

Suponiendo que los criterios a utilizar son =Centro 2 y Categoría=Llave plana, las fórmulas que tendrías que utilizar serían las siguientes:

Para la cuenta

=SUMAPRODUCTO(–(A2:A13=”Centro 2″);–(B2:B13=”Llave plana”))

Ó

=SUMAPRODUCTO((A2:A13=”Centro 2″)*(B2:B13=”Llave plana”))

Para la suma

=SUMAPRODUCTO(–(A2:A13=”Centro 2″);–(B2:B13=”Llave plana”);C2:C13)

Ó

=SUMAPRODUCTO((A2:A13=”Centro 2″)*(B2:B13=”Llave plana”);C2:C13)

Para el promedio

=SUMAPRODUCTO(–(A2:A13=”Centro 2″);–(B2:B13=”Llave plana”);C2:C13)/SUMAPRODUCTO(–(A2:A13=”Centro 2″);–(B2:B13=”Llave plana”))

Ó

=SUMAPRODUCTO((A2:A13=”Centro 2″)*(B2:B13=”Llave plana”);C2:C13)/SUMAPRODUCTO((A2:A13=”Centro 2″)*(B2:B13=”Llave plana”))

SUMAPRODUCTO con uno o varios criterios O

En cada ejemplo anterior se debían cumplir las dos condiciones para que SUMAPRODUCTO sumase el resultado de cada producto.

También es posible establecer criterios con el operador O, que significa que debe cumplirse como mínimo un criterio. El operador O se especifica introduciendo un signo + entre las matrices.

Con los datos de la imagen anterior, las operaciones de suma y cuenta, en las que los criterios utilizados son Categoría=Llave estrella Ó Categoría=Llave plana, quedarían de la siguiente forma:

Para lacuenta

=SUMAPRODUCTO((B2:B13=”Llave estrella”)+(B2:B13=”Llave plana”))

Para la suma

=SUMAPRODUCTO((B2:B13=”Llave estrella”)+(B2:B13=”Llave plana”);C2:C13)

Teniendo en cuenta que el símbolo * equivale al operador Y y el símbolo + equivale a O, no te será muy difícil imaginar cómo podrías combinarlos para conseguir condiciones más complejas.

Resumen

SUMAPRODUCTO es una de las funciones más potentes de Excel… Estos ejemplos que te he mostrado en el artículo son solo la punta del iceberg de lo que puedes conseguir con ella.

Este artículo es ya bastante extenso así que dejaré para el siguiente algunos ejemplos avanzados que apuesto a que te sorprenderán. Ya estoy trabajando en ellos.