Autor: Oscar Cronquist Artículo actualizado en marzo 03, 2020

La función SUMPRODUCT calcula el producto de los valores correspondientes y luego devuelve la suma de cada multiplicación.

La imagen de arriba muestra cómo funciona SUMPRODUCT con mayor detalle.

Fórmula en la celda B7:

=SUMPRODUCT(B2:B4, C2:C4)

La función SUMPRODUCT multiplica los rangos de celdas fila por fila y luego agrega los números y devuelve un total. La fórmula en la celda B7 multiplica los valores 1 * 4 = 4, 2 * 5 = 10 y 3 * 6 = 18 y luego suma los números 4 + 10 + 18 igual a 32.

Sintaxis de la función de Excel

SUMPRODUCTO (array1, [array2], ...)

Argumentos

array1 Necesario. Necesario. El primer argumento de matriz cuyos números desea multiplicar y luego sumar.
[array2] Opcional. Hasta 254 argumentos adicionales.

Comentarios

La función SUMPRODUCT es una de las funciones más poderosas en Excel y es una que uso con frecuencia. Recomiendo aprender cómo funciona.

La función SUMPRODUCT requiere que lo ingrese como una fórmula regular, no como una fórmula de matriz. Sin embargo, hay excepciones. Si usa una expresión lógica, debe ingresar la fórmula como una fórmula de matriz y convertir los valores booleanos a sus equivalentes.

Hay soluciones a este problema que demostraré en los ejemplos a continuación.

Tenga en cuenta que los suscriptores de Excel 365 no necesitan ingresar fórmulas como fórmulas de matriz en los ejemplos a continuación. Microsoft ha cambiado la forma en que funcionan las fórmulas de matriz y ahora se llaman "matrices dinámicas".

Ejemplo 1 - Los fundamentos

Función SUMPRODUCT1

Este ejemplo demuestra cómo funciona la función SUMPRODUCT.

Fórmula en la celda B7:

=SUMPRODUCT(B2:B4, C2:C4)

Paso 1 - Multiplicar valores en la misma fila

La primera matriz está en el rango de celdas B2: B4 y la segunda matriz está en el rango de celdas C2: C4.

B2: B4 * C2: C4

se convierte en

{1;2;3} * {4;5;6}

se convierte en

{1*4; 2*5; 3*6}

y devuelve {4; 10; 18}.

Los mismos cálculos se realizan en la columna D y se muestran en la columna E, ver la imagen de arriba.

Paso 2 - Devuelve la suma de esos productos

{4; 10; 18}

se convierte en

+ + 4 10 18

y la función devuelve 32 en la celda B7.

Se realiza el mismo cálculo E5, la suma de los productos en el rango de celdas E2: E4 se calcula en la celda E5. Ver la imagen de arriba.

Ahora ya sabes lo básico. Pasemos a algo más interesante.

Ejemplo 2 - Usa una expresión lógica

Función SUMPRODUCT2

La imagen de arriba muestra una fórmula en la celda G4 que cuenta cuántas celdas es igual al valor en la celda G2. Tenga en cuenta que esto es solo un ejemplo. Te recomiendo que uses el Función COUNTIF para contar las células en función de una condición, está diseñado para hacer eso.

Fórmula en celda G4:

=SUMPRODUCT(--(B2:B6=$G$2))

Paso 1: la expresión lógica devuelve un valor booleano que debemos convertir a números

Solo hay una matriz en esta fórmula, pero algo más está distorsionando la imagen. Un operador de comparación (signo igual) y un segundo valor de celda (G2) o un valor de comparación. Con estos, ahora hemos construido una expresión lógica. Esto significa que el valor en la celda G2 se compara con todos los valores en el rango de celdas B2: B6 (no distingue entre mayúsculas y minúsculas).

B2: B6 = $ G $ 2

se convierte en

{"Alaska"; "California"; "Arizona"; "California"; "Colorado"} = "California"

y devoluciones

{FALSO; CIERTO; FALSO; CIERTO; FALSO}.

Todos son valores booleanos y Excel no puede sumar estos valores. Tenemos que convertir los valores a valores numéricos. Hay algunas opciones, puedes:

  • Agregue un cero - (B2: B6 = $ G $ 2) + 0
  • Multiplica con 1 - (B2: B6 = $ G $ 2) * 1
  • Signos negativos dobles - (B2: B6 = $ G $ 2)

Todos ellos convierten valores booleanos a sus equivalentes numéricos. VERDADERO es 1 y FALSO es 0 (cero).

- ({FALSO; VERDADERO; FALSO; VERDADERO; FALSO})

se convierte en

{0;1;0;1;0}

Paso 2 - Devuelve la suma de esos productos

{0;1;0;1;0}

se convierte en

0 + 1 + 0 + 1 + 0

y devuelve 2 en la celda G4. Hay dos celdas que contienen el valor "California" en el rango de celdas B2: B6.

Puede lograr lo mismo con la función COUNTIF o contar varios valores en diferentes columnas con Función COUNTIFS. De hecho, usted puede contar registros completos en un conjunto de datos usando la función COUNTIFS.

Ejemplo 3 - Use múltiples condiciones en la función SUMPRODUCT

Función SUMPRODUCT3

Este ejemplo muestra cómo usar múltiples condiciones en la función SUMPRODUCT usando la lógica AND. Y la lógica significa que todas las condiciones deben cumplirse en una fila dada para agregar el número al total.

Fórmula en la celda D10:

=SUMPRODUCT(--(B2:B8=B10), --(C2:C8=C10),D2:D8)

Esta fórmula contiene tres argumentos, la función SUMPRODUCT le permite usar hasta 30 argumentos. Puedes hacer la fórmula algo más corta:

=SUMPRODUCT((B2:B8=B10)*(C2:C8=C10)*D2:D8)

Esto también le permite tener muchas más condiciones que 30 si lo desea y usar la lógica OR si lo desea. El ejemplo 4 demuestra la lógica OR.

Es solo la memoria disponible de la computadora el límite si usa este método. La fórmula se ve como una fórmula de matriz, pero no, no es necesario que la ingrese como una fórmula de matriz.

Paso 1 - Multiplicando los componentes correspondientes en los arreglos dados

La primera expresión lógica B2: B8 = B10 usa un signo igual para verificar si los valores en el rango de celda B2: B8 son iguales al valor en la celda B10.

B2: B8 = B10

se convierte en

{"Alaska"; "California"; "Arizona"; "California"; "Colorado"; "California"; "Nevada"} = "California"

y devoluciones

{FALSO; CIERTO; FALSO; CIERTO; FALSO; CIERTO; FALSO}.

La segunda expresión lógica es C2: C8 = C10, los otros operadores lógicos que puede usar son:

  • = igual
  • <menos que
  • > mayor que
  • <> no es igual a
  • <= menor o igual que
  • > = mayor o igual que

C2: C8 = C10

se convierte en

{"Anchorage"; "San Diego"; "Phoenix"; "Los Angeles"; "Denver"; "Los Angeles"; "Las Vegas"} = "Los Angeles"

y devoluciones

{FALSO; FALSO; FALSO; CIERTO; FALSO; CIERTO; FALSO}.

La tercera y última lógica

(B2:B8=B10)*(C2:C8=C10)*D2:D8

se convierte en

({FALSO; VERDADERO; FALSO; VERDADERO; FALSO; VERDADERO; FALSO}) * ({FALSO; FALSO; FALSO; VERDADERO; FALSO; VERDADERO; FALSO}) * {10; 20; 40; 10; 20; 30; 10}

se convierte en

{0;0;0;1;0;1;0}*{10; 20; 40; 10; 20; 30; 10}

y devoluciones

{0; 0; 0; 10; 0; 30; 0}

Paso 2 - Devuelve la suma de esos productos

{0; 0; 0; 10; 0; 30; 0}

se convierte en

10 + 30

y devuelve 40 en la celda D10.

Ejemplo 4 - Usa múltiples expresiones lógicas y operadores matemáticos

Los operadores matemáticos entre matrices te permiten hacer cálculos más complicados, como este:

Función SUMPRODUCT4

Este ejemplo muestra cómo usar múltiples condiciones. Se agrega un número en el rango de celdas D2: D8 si se encuentra California en la fila correspondiente en el rango de celdas B2: B8 O si se encuentra "Las Vegas" en el rango de celdas C2: C8.

* - Ambas expresiones lógicas deben coincidir (lógica AND)

+ - Cualquiera de las expresiones lógicas debe coincidir, puede ser todo, solo puede ser una. No importa. (O lógica)

La lógica AND detrás de esto es que

  • VERDADERO * VERDADERO = VERDADERO
  • VERDADERO * FALSO = FALSO
  • FALSO * FALSO = FALSO

La lógica OR funciona así:

  • VERDADERO + VERDADERO = VERDADERO
  • VERDADERO + FALSO = VERDADERO
  • FALSO + FALSO = FALSO

Fórmula en la celda D10:

=SUMPRODUCT(((B2:B8=B10)+(C2:C8=C10))*D2:D8)

Estas expresiones verifican si California se encuentra en el rango de celdas B2: B8 o Las Vegas se encuentra en el rango de celdas C2: C8. Se encuentran en las filas 3, 5,7 y 8.

Función SUMPRODUCT O lógica

La función sumproduct suma los valores correspondientes en la columna D y devuelve 70 en la celda D10. 20 + 10 + 30 + 10 es igual a 70.

Descargar el archivo de Excel


Sumproduct-function.xlsx

Artículos recomendados