SUMAPRODUCTO – ¿Cómo Funciona? Arrays, Criterios – Excel y Google Sheets

Este tutorial muestra cómo utilizar la Función SUMAPRODUCTO en Excel y Google Sheets.

 

Descripción de la Función SUMAPRODUCTO

La función SUMAPRODUCTO multiplica matrices de números y suma la matriz resultante.

Es una de las funciones más potentes de Excel. Su nombre, podría llevarle a creer que sólo está pensada para cálculos matemáticos básicos (media ponderada), pero puede utilizarse para mucho más.

Matemáticas Básicas

Veamos un ejemplo básico de SUMAPRODUCTO, utilizándolo para calcular las ventas totales.

 

Tenemos nuestra tabla de productos, y queremos calcular el total de ventas. Estaremos tentados de añadir una nueva columna, tomar la cantidad vendida * precio y luego sumar la nueva columna. Sin embargo, en lugar de eso, puedes utilizar simplemente la función SUMPRODUCT. Veamos la fórmula:

=SUMPRODUCTO(A2:A4,B2:B4)

La función cargará los rangos de números en matrices, los multiplicará entre sí y luego sumará los resultados:

=PRODUCTOSUMA({100; 50; 10}; {6; 7; 5})
=PRODUCTOSUMA({100 * 6; 50 * 7; 10 * 5})
=PRODUCTOSUMA({600; 350; 50}
=1000

La función SUMAPRODUCTO fue capaz de multiplicar todos los números por nosotros y hacer la suma.

Media Ponderada

Otro caso en el que es útil utilizar SUMAPRODUCTO es cuando necesitas calcular una media ponderada. Esto ocurre con mayor frecuencia cuando se trata de trabajos escolares, así que consideremos la siguiente tabla.

 

Podemos ver cuánto valen las pruebas, los exámenes y los deberes para la nota global, así como cuál es la media actual de cada elemento en particular. Podemos calcular la nota global escribiendo

=SUMAPRODUCTO(B2:B4; C2:C4)

Nuestra función vuelve a multiplicar cada elemento de las matrices antes de sumar el total. Esto funciona así

=SUMAPRODUCTO({30%; 50%; 20%}; {73%; 90%; 95%})
=SUMAPRODUCTO({22%; 45%; 19%})
=86%

Columnas Múltiples

Otro lugar donde podemos utilizar SUMAPRODUCTO es con más columnas que necesitan ser multiplicadas entre sí. Veamos un ejemplo en el que necesitamos calcular el volumen en piezas de madera.

En lugar de crear una columna de ayuda para calcular la venta total de cada fila, podemos hacerlo con una sola fórmula. Nuestra fórmula será

=SUMAPRODUCTO(B2:B5;C2:C5;D2:D5)

Los primeros elementos de cada fila se multiplicarán entre sí (por ejemplo, 4 * 2 * 1 = 8). Luego, los segundos (4 * 2 * 2 = 16), y los terceros, etc. En total, esto producirá la matriz de productos que se parecen a {8, 16, 16, 32). Entonces el volumen total sería la suma de esa matriz, 72.

Arrays

SUMAPRODUCTO requiere entradas de arrays.

En primer lugar, ¿qué entendemos por «array»? Un array es simplemente un grupo de elementos (por ejemplo, números) dispuestos en un orden específico, al igual que un rango de celdas. Así, si tienes los números 1, 2, 3 en las celdas A1:A3, Excel lo leerá como una matriz {1;2;3}. De hecho, puedes introducir {1;2;3} directamente en las fórmulas de Excel y reconocerá la matriz.

Hablaremos más sobre las matrices más adelante.

Un Criterio

Bien, añadamos otra capa de complejidad. Hemos visto que SUMAPRODUCTO puede manejar arrays de números, pero ¿qué pasa si queremos comprobar criterios? Bueno, también puedes crear arrays para valores booleanos (los valores booleanos son valores que son VERDADERO o FALSO).

Por ejemplo, tomemos un array básico {1;2;3}. Creemos un array correspondiente que indique si cada número es mayor que 1. Este array se vería como {FALSO; VERDADERO; VERDADERO}. Esto es extremadamente útil en las fórmulas, porque podemos convertir fácilmente VERDADERO / FALSO en 1 / 0.

Veamos un ejemplo. Usando la tabla de abajo, queremos calcular «¿Cuántas unidades vendidas fueron rojas?»

Podemos hacerlo, con esta fórmula

=SUMAPRODUCTO(A2:A4; --(B2:B4="Rojo"))

«¡Espera! ¿Qué es ese doble símbolo de menos?», dirás. ¿Recuerdas que dije que podíamos convertir de VERDADERP/FALSO a 1/0? Lo hacemos forzando al ordenador a realizar una operación matemática. En este caso, estamos diciendo «toma el valor negativo, y luego toma el negativo de nuevo». Escribiendo eso, nuestra matriz va a cambiar así:

{Verdadero; Verdadero; Falso}
{-1; -1; 0}
{1; 1; 0}

Así que, volviendo a la fórmula completa de SUMAPRODUCTO, va a cargar nuestras matrices y luego multiplicar, así

=SUMAPRODUCTO({100; 50; 10}; {1; 1; 0})
=SUMAPRODUCTO({100; 50; 0})
=150

Observa cómo el 3er elemento se convierte en un 0, porque cualquier cosa multiplicada por 0 se convierte en cero.

Criterios Múltiples

Podemos cargar hasta 255 matrices en nuestra función, así que ciertamente podemos cargar más criterios. Veamos esta tabla más grande donde hemos añadido el Mes vendido.

Si queremos saber cuántos artículos vendidos eran rojos y estaban en el mes de febrero, podríamos escribir nuestra fórmula como

=SUMAPRODUCTO(A2:A4; --(B2:B4="Rojo"); --(C2:C4="Feb"))

El ordenador evaluaría entonces nuestras matrices y las multiplicaría. Ya hemos visto cómo las matrices Verdadero/Falso se convierten en 1/0, así que voy a omitir ese paso por ahora.

=SUMAPRODUCTO({100; 50; 10}; {1; 1; 0}; {0; 1; 1})
=SUMAPRODUCTO({0; 50; 0})
=50

En nuestro ejemplo sólo teníamos una fila que cumplía todos los criterios, pero con datos reales, es posible que tenga varias filas que necesite sumar.

Criterios Complejos

Bien, hasta este punto, puede que no estés impresionado porque todos nuestros ejemplos podrían haberse hecho usando otras funciones como SUMAR.SI.CONJUNTO o CONTAR.SI.CONJUNTO. Ahora vamos a hacer algo que esas otras funciones no pueden hacer. Anteriormente, nuestra columna Mes tenía los nombres reales de los meses. ¿Qué pasaría si en su lugar tuviera fechas?

 

No podemos hacer un SUMAR.SI.CONJUNTO ahora, porque SUMAR.SI.CONJUNTO no puede manejar los criterios que necesitamos. Sin embargo, SUMAPRODUCTO puede manejarnos manipulando el array, y haciendo una prueba más profunda. Ya hemos estado manipulando arrays cuando hemos traducido el Verdadero/Falso a 1/0. Vamos a manipular este array con la función MES. Esta es la fórmula completa que vamos a utilizar

=SUMAPRODUCTO(A2:A4; --(B2:B4="Rojo"); --(MES(C2:C4)=2))

Veamos la 3era matriz con más detalle. En primer lugar, nuestra fórmula va a extraer el número del mes de cada fecha en C2:C4. Esto nos dará {1; 2; 2}. A continuación, comprobamos si ese valor es igual a 2. Ahora nuestra matriz se parece a {Falso, Verdadero, Verdadero}. Volvemos a hacer el doble menos, y tenemos {0; 1; 1}. Ahora estamos de vuelta en un lugar similar al que teníamos en el Ejemplo 3, y nuestra fórmula podrá decirnos que hubo 50 unidades vendidas en febrero que eran rojas.

Doble Menos vs. Multiplicación

Si has visto la función SUMAPRODUCTO en uso antes, puede que hayas visto una notación ligeramente diferente. En lugar de utilizar un doble menos, puedes escribir

=SUMAPRODUCTO(A2:A4*(B2:B4="Rojo")*(MES(C2:C4)=2))

La fórmula va a seguir funcionando de la misma manera, sólo le estamos diciendo manualmente al ordenador que queremos multiplicar las matrices. SUMAPRODUCTO iba a hacer esto de todos modos, así que no hay ningún cambio en el funcionamiento de las matemáticas. Realizar la operación matemática convierte nuestro Verdadero/Falso en 1/0 igual. Entonces, ¿por qué la diferencia?

La mayoría de las veces, no importa demasiado, y se reduce a la preferencia del usuario. Sin embargo, hay al menos un caso en el que es necesario multiplicar.

Cuando se utiliza SUMAPRODUCTO, el ordenador espera que todos los argumentos (array1, array2, etc.) tengan el mismo tamaño. Esto significa que tienen el mismo número de filas o columnas. Sin embargo, puedes hacer lo que se conoce como un cálculo de array bidimensional con SUMAPRODUCTO que veremos en el siguiente ejemplo. Cuando haces eso, los arrays son de diferentes tamaños, por lo que necesitamos saltarnos esa comprobación de «todos del mismo tamaño».

Dos Dimensiones

En todos los ejemplos anteriores nuestros arrays iban en la misma dirección. SUMAPRODUCTO puede manejar cosas que van en dos direcciones, como veremos en la siguiente tabla.

 

Aquí está nuestra tabla de unidades vendidas, pero los datos están reordenados donde las categorías van en la parte superior. Si queremos saber cuántos artículos eran rojos y de la categoría A, podemos escribir

=SUMAPRODUCTO((A2:A4="Rojo")*(B1:C1="A")*B2:C4)

¿Qué ocurre aquí? Resulta que vamos a multiplicar en dos direcciones diferentes. Visualizar esto es más difícil con sólo una frase escrita, así que tenemos unas cuantas imágenes para ayudarnos. En primer lugar, nuestro criterio de fila (¿es Rojo?) va a multiplicar a través de cada fila de la matriz.

=SUMAPRODUCTO((A2:A4="Rojo")*B2:C4)

A continuación, el criterio de la columna (¿es la categoría A?) se multiplicará por cada columna

=SUMAPRODUCTO((A2:A4="Rojo")*(B1:C1="A")*B2:C4)

Después de que ambos criterios hayan hecho su trabajo, los únicos no ceros que quedan son el 5 y el 10. SUMAPRODUCTO nos dará entonces el total de 15 como respuesta.

¿Recuerdas que hablamos de que las matrices deben tener el mismo tamaño a menos que estés haciendo dos dimensiones? Eso era parcialmente correcto. Mira de nuevo los arrays que usamos en nuestra fórmula. La altura de dos de nuestros arrays es la misma, y el ancho de dos de nuestros arrays es el mismo. Por lo tanto, usted todavía tiene que asegurarse de que las cosas van a alinear correctamente, pero usted puede hacerlo en diferentes dimensiones.

Dos Dimensiones – Complejo

Muchas veces se nos presentan datos que no están en la mejor disposición adecuada para nuestras fórmulas. Podríamos intentar reorganizarlos manualmente, o podemos ser más inteligentes con nuestras fórmulas. Consideremos la siguiente tabla.

 

Aquí tenemos los datos de nuestros artículos y ventas mezclados para cada mes. ¿Cómo podríamos averiguar cuántos artículos ha vendido Bob en todo el año?

Para ello, utilizaremos dos funciones adicionales: HALLAR y ESNUMERO. La función HALLAR nos va a permitir buscar nuestra palabra clave «Items» dentro de las celdas de la cabecera. La salida de esta función será un número o un error (si no se encuentra la palabra clave). Entonces, usaremos el ESNUMERO para convertir esa salida en nuestros valores booleanos. Nuestra fórmula se verá como la siguiente.

Ya deberías estar bastante familiarizado con el primer array. Va a crear una salida como {0; 1; 0; 1}. La siguiente matriz de criterios de la que acabamos de hablar. Va a crear un número para todas las celdas con «Items» en ellas, y un error para las otras {5; #N/A!; 5; #N/A!}. El ESNUMERO entonces convierte esto en booleano {Verdadero; Falso; Verdadero; Falso}. Luego, al multiplicar, sólo se mantendrán los valores de la primera y tercera columna. Después de que todas las matrices se multipliquen entre sí, los únicos números distintos de cero que tendremos son los que se destacan aquí:

=SUMAPRODUCTO((A2:A5="Bob")*(ESNUMERO(HALLAR("Items";B1:E1))*B2:E5))

 

El SUMAPRODUCTO sumará todos estos valores y obtendremos el resultado final de 29.

SUMAPRODUCTO O

Muchas situaciones surgen donde nos gustaría ser capaces de sumar valores si nuestra columna de criterios tiene un valor O otro valor. Esto se puede lograr en SUMAPRODUCTO sumando dos matrices de criterios entre sí. En este ejemplo, queremos sumar las unidades vendidas de rojo y azul.

Nuestra fórmula será la siguiente:

=SUMAPRODUCTO(A2:A7; (B2:B7="Rojo")+(B2:B7="Azul"))

 

Veamos el array de criterios Rojo. Producirá una matriz con el siguiente aspecto {1; 1; 0; 0; 0; 0}. La matriz de criterios Azul tendrá el siguiente aspecto: {0; 0; 1; 0; 1; 0}. Cuando los sumas, la nueva matriz se verá como {1; 1; 1; 0; 1; 0}. Podemos ver cómo las dos matrices se han mezclado en una única matriz de criterios. La función multiplicará eso por nuestra primera matriz, y obtendremos {100; 50; 10; 0; 75; 0}. Fíjate en que los valores de Verde se han reducido a cero. El paso final del SUMAPRODUCTO es sumar todos los números para llegar a nuestra solución de 235.

Una palabra de precaución aquí. Tenga cuidado cuando las matrices de criterios no son mutuamente excluyentes. En nuestro ejemplo, los valores de la columna B podrían ser Rojo o Azul, pero sabíamos que nunca podrían ser ambos. Considere si hubiéramos escrito esta fórmula

=SUMAPRODUCTO(A2:A7; (A2:A7>=50)+(B2:B7="Azul"))

Nuestra intención es encontrar los artículos azules que se vendieron o que estaban en una cantidad superior a 50. Sin embargo, estas condiciones no son excluyentes, ya que una misma fila podría tener más de 50 en la columna A y ser azul. Esto daría como resultado que la primera matriz de criterios fuera {1; 1; 0; 1; 1; 0}, y la segunda matriz de criterios fuera {0; 0; 1; 0; 1; 0}. Al sumarlos se obtiene {1; 1; 1; 1; 2; 0}. ¿Ves como ahora tenemos un 2 ahí? Si se dejara solo, el SUMAPRODUCTO acabaría duplicando el valor de esa fila, cambiando el 75 por un 150, y obtendríamos un resultado erróneo. Para corregir esto, colocamos una comprobación de criterios externa en nuestra matriz, así:

=SUMAPRODUCTO(A2:A7; --((A2:A7>=50)+(B2:B7="Azul")>0))

Ahora, una vez sumados los dos arrays de criterios internos, comprobaremos si el resultado es mayor que 0. Esto elimina el 2 que teníamos antes, y en su lugar tendremos un array como {1; 1; 1; 1; 1; 0} que producirá el resultado correcto.

SUMAPRODUCTO IGUAL

La mayoría de las funciones de Excel no distinguen entre mayúsculas y minúsculas, pero a veces necesitamos poder hacer una búsqueda teniendo en cuenta las mayúsculas y minúsculas. Cuando el resultado deseado es numérico, podemos conseguirlo utilizando la función IGUAL dentro de la función SUMAPRODUCTO. Considere la siguiente tabla:

Queremos encontrar la puntuación del elemento «ABC123». Normalmente, la función IGUALcomparará dos elementos y devolverá una salida booleana indicando si los dos elementos son exactamente iguales. Sin embargo, como estamos dentro de un SUMAPRODUCTO, nuestro ordenador sabrá que estamos tratando con arrays y podrá comparar un elemento con cada elemento de un array. Nuestra fórmula tendrá el siguiente aspecto

=SUMAPRODUCTO(--IGUAL("ABC123"; A2:A5); B2:B5)

La función IGUAL comprobará cada elemento de A2:A5 para ver si coincide con el valor y el caso. Esto producirá una matriz que se parece a {0; 1; 0; 0}. Cuando se multiplica contra B2:B5, la matriz se convierte en {0; 2; 0; 0}. Después de la suma final, obtenemos nuestra solución de 2.

SUMAPRODUCTO en Google Sheets

La función SUMAPRODUCTO funciona exactamente igual en Google Sheets que en Excel:

 

Ejemplos de SUMAPRODUCTO en VBA

También puedes utilizar la función SUMPRODUCT en VBA. Tipo:

Application.WorksheetFunction.Sumproduct(array1,array2,array3)

Ejecutando las siguientes sentencias VBA:

Range("B10") = Application.WorksheetFunction.SumProduct(Range("A2:A7"), Range("B2:B7"))

producirá los siguientes resultados

Para los argumentos de la función (array1, etc.), puedes introducirlos directamente en la función, o definir variables para utilizarlos en su lugar.