Saltar al contenido
GridBelt
  • Prueba de Excel
  • Práctica
  • Programas
  • Precios
Iniciar sesión
Empezar la prueba gratuita
  • Prueba de Excel
  • Práctica
  • Programas
  • Precios
Iniciar sesiónCrear cuenta
Empezar la prueba gratuita
GridBelt

Practica Excel en tu navegador.

Prueba de Excel

  • Nivel 1: Principiante
  • Nivel 2: Intermedio
  • Nivel 3: Experto
  • Prueba de Excel en 7 días
  • Clasificación
  • Prueba de Excel para entrevistas
  • Evaluación de Excel de Indeed

Práctica

  • Todos los ejercicios
  • Programas
  • Para principiantes

Guías

  • Fórmulas de Excel
  • Atajos de Excel

Empresa

  • Acerca de
  • Precios
  • Cómo corregimos

© GridBelt. Todos los derechos reservados.

  • Política de privacidad
  • Términos de uso
  • Política de reembolso

Excel es una marca comercial de Microsoft Corporation. Indeed, TestGorilla, SHL y otras marcas son propiedad de sus respectivos titulares. GridBelt es un sitio de práctica independiente y no está afiliado a Microsoft, TestGorilla, SHL ni Indeed, ni respaldado ni patrocinado por ellos.

  1. Práctica
  2. /
  3. SUMIF and SUMIFS

SUMPRODUCT practice: weighted average price over 50 purchase lots

  • Experto
  • ~8 min
  • SUMIF and SUMIFS

Enunciado en inglés

A coffee roaster bought 50 lots of green coffee in 2026 from 6 importers, listed in A2:F51 with the quantity in kilograms and the price paid per kilogram. Prices range from $4.26 to $12.63 per kg and lots from 60 kg to 840 kg: the cheap Brazil lots average 583 kg, the expensive Kenya lots 197 kg, and every importer gives a volume discount, so within one origin the big lots are the cheap ones too. A plain average of the price column ($8.04) therefore overstates what a kilogram really cost. Finance needs the total spend, the true average cost per kilogram for the year-end stock valuation, and the same figure for the origin typed in I5.

Qué tienes que hacer

  1. 1Click I2 and type =SUMPRODUCT(E2:E51,F2:F51). SUMPRODUCT multiplies each quantity by its price and adds the 50 products in one step — the total spend.
  2. 2Click I3 and divide that total by the kilograms bought: =SUMPRODUCT(E2:E51,F2:F51)/SUM(E2:E51). This is the quantity-weighted average price per kg; compare it with I4, which already holds =AVERAGE(F2:F51).
  3. 3Click I6 for the origin in I5: multiply the quantities by the test (D2:D51=I5) inside SUMPRODUCT, =SUMPRODUCT((D2:D51=I5)*E2:E51,F2:F51), and divide by the kilograms of that origin, SUMIF(D2:D51,I5,E2:E51). AVERAGEIF(D2:D51,I5,F2:F51) would average the prices without weights.
  4. 4Press Enter after each formula; no Ctrl+Shift+Enter (⌘+Shift+Enter on Mac) is needed — SUMPRODUCT handles the arrays itself in every Excel version.
  5. 5Type Kenya in I5: I6 changes to $11.87, the Kenya figure. Put Colombia back before you check.

Celdas de respuesta

Corrección

Escribe tu fórmula en la cuadrícula, presiona Enter y luego haz clic en Comprobar. Evaluamos el valor de cada celda de respuesta y la fórmula que lo produce.

Tareas

  1. Sin comprobarIn I2, use SUMPRODUCT to compute the total spend: every quantity in E2:E51 times its price in F2:F51, added up.
  2. Sin comprobarIn I3, use SUMPRODUCT to compute the weighted average price per kg: total spend divided by the total kilograms in E2:E51.
  3. Sin comprobarIn I6, use SUMPRODUCT with a condition to compute the weighted average price per kg of the origin in I5 only (spend on that origin divided by its kilograms).

Para las tareas de hoja de cálculo se recomienda una pantalla grande. La hoja se desplaza dentro de este recuadro.

StartInsertFormulasDataView
A1fxPreparando tu libro…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Escribe tu fórmula en la hoja y luego comprueba.

Sobre este ejercicio

Un conjunto de datos de 50 filas en una hoja, 3 celdas de respuesta, unos 8 minutos. Se corrige en tu navegador: el valor y la fórmula que lo produce, con comentarios que te dicen qué arreglar.

Cómo resolverlo

  • I2=SUMPRODUCT(E2:E51,F2:F51)
  • I3=SUMPRODUCT(E2:E51,F2:F51)/SUM(E2:E51)
  • I6=SUMPRODUCT((D2:D51=I5)*E2:E51,F2:F51)/SUMIF(D2:D51,I5,E2:E51)

SUMPRODUCT takes ranges of the same size, multiplies them pair by pair and adds the products, so =SUMPRODUCT(E2:E51,F2:F51) is the total spend — the same as writing a quantity × price column and summing it, in one cell. Dividing that total by the total kilograms gives the weighted average: each lot's price counts in proportion to its size, which is why the result ($6.92) is lower than the plain AVERAGE of the price column ($8.04). A test such as (D2:D51=I5) inside SUMPRODUCT turns into 1 for the matching rows and 0 for the rest, so only that origin's spend is added; SUMIF supplies the matching kilograms.

Pitfall 1: =AVERAGE(F2:F51) is only right when every lot has the same quantity; with unequal weights it leans towards the small lots — here the small, dear Kenya lots pull it up, and the volume discount does the same inside each origin (AVERAGEIF gives $8.18 for Colombia instead of $7.91). Pitfall 2: the ranges must be the same shape, otherwise SUMPRODUCT returns #VALUE!; a text cell such as "n/a" counts as 0 in the comma form (SUMPRODUCT(E,F)) but produces #VALUE! in the multiplication form (SUMPRODUCT(E*F)). Pitfall 3: a condition on its own, SUMPRODUCT(D2:D51=I5,E2:E51), multiplies TRUE/FALSE values that SUMPRODUCT treats as 0 — multiply the test by a number range ((D2:D51=I5)*E2:E51) or coerce it with -- first. Alternatives: =SUM(E2:E51*F2:F51) does the same in Excel 365 (dynamic arrays) and needs Ctrl+Shift+Enter in Excel 2019; a helper column G = E2*F2 with =SUM(G2:G51)/SUM(E2:E51) is easier to audit and what many finance teams prefer; =SUMIFS(G2:G51,D2:D51,I5)/SUMIFS(E2:E51,D2:D51,I5) is the helper-column version of the origin figure. Rounding: keep the division unrounded for the valuation and format the cell as currency; =ROUND(...,2) is accepted here because the tolerance is half a cent.

Otras formas válidas

  • =SUMPRODUCT(E2:E51*F2:F51)/SUM(E2:E51) — the multiplication form of SUMPRODUCT
  • =SUMPRODUCT(--(D2:D51=I5),E2:E51,F2:F51)/SUMIF(D2:D51,I5,E2:E51) — the origin figure with the test coerced by --
  • =SUMPRODUCT((D2:D51=I5)*E2:E51*F2:F51)/SUMPRODUCT((D2:D51=I5)*E2:E51) — the origin figure with SUMPRODUCT on both sides
  • =SUM(E2:E51*F2:F51)/SUM(E2:E51) — array multiplication inside SUM (Excel 365); not accepted here because the task names SUMPRODUCT

Errores frecuentes

Using =AVERAGE(F2:F51) for the average price: it gives $8.04 instead of $6.92 because the 60 kg lots and the 840 kg lots count the same — the quantities must weight the prices.

Cómo corregimos

Se acepta cualquier fórmula que produzca el resultado correcto, salvo que la tarea nombre una función: entonces otra función obtiene media puntuación. Comparamos los valores con una pequeña tolerancia y, en el texto, ignoramos mayúsculas y espacios. Un número correcto escrito a mano en lugar de una fórmula obtiene media puntuación. Las tareas de varias celdas obtienen la puntuación completa con el 100% de las celdas correctas y media puntuación desde el 50%. Cuando importa, también cambiamos una celda de entrada en una copia de tu hoja y comprobamos que tu fórmula se adapta.

Respuestas calculadas con las reglas de Excel 365 y comprobadas en nuestro corrector el 16 de noviembre de 2026. Autor: GridBelt team.

En estos programas

  • Excel test in 7 days, Plan del nivel 2Día 6
  • Excel for Data AnalystsDía 10
  • Excel for Sales and MarketingDía 8

Ejercicios relacionados

  • Sales by region with SUMIFPrincipiante, ~5 min
  • Sales by region and product with SUMIFSIntermedio, ~7 min
  • Average and largest deal per rep (AVERAGEIFS, MAXIFS)Intermedio, ~8 min
  • Total of orders above $500Principiante, ~4 min

Referencia de la función

Documentación oficial

Siguiente ejercicio

Highlight overdue invoices (conditional formatting)