Aller au contenu
GridBelt
  • Test Excel
  • Exercices
  • Programmes
  • Tarifs
Se connecter
Commencer le test gratuit
  • Test Excel
  • Exercices
  • Programmes
  • Tarifs
Se connecterCréer un compte
Commencer le test gratuit
GridBelt

Entraînez-vous sur Excel dans votre navigateur.

Test Excel

  • Niveau 1 : Débutant
  • Niveau 2 : Intermédiaire
  • Niveau 3 : Expert
  • Test Excel en 7 jours
  • Classement
  • Test Excel en entretien d’embauche
  • Évaluation Excel d’Indeed

Exercices

  • Tous les exercices
  • Programmes
  • Pour débutants

Guides

  • Formules Excel
  • Raccourcis Excel

Entreprise

  • À propos
  • Tarifs
  • Comment nous corrigeons

© GridBelt. Tous droits réservés.

  • Politique de confidentialité
  • Conditions d’utilisation
  • Politique de remboursement

Excel est une marque commerciale de Microsoft Corporation. Indeed, TestGorilla, SHL et les autres marques sont la propriété de leurs détenteurs respectifs. GridBelt est un site d’entraînement indépendant ; il n’est affilié ni à Microsoft, ni à TestGorilla, ni à SHL, ni à Indeed, et n’est ni cautionné ni sponsorisé par ces sociétés.

  1. Exercices
  2. /
  3. SUMIF and SUMIFS

SUMPRODUCT practice: weighted average price over 50 purchase lots

  • Expert
  • ~8 min
  • SUMIF and SUMIFS

Énoncé en anglais

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.

Ce que vous devez faire

  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.

Cellules de réponse

Correction

Saisissez votre formule dans la grille, appuyez sur Enter, puis cliquez sur Vérifier. Nous corrigeons la valeur de chaque cellule de réponse et la formule qui la produit.

Tâches

  1. Non vérifiéIn I2, use SUMPRODUCT to compute the total spend: every quantity in E2:E51 times its price in F2:F51, added up.
  2. Non vérifiéIn I3, use SUMPRODUCT to compute the weighted average price per kg: total spend divided by the total kilograms in E2:E51.
  3. Non vérifiéIn 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).

Ordinateur recommandé pour les tâches dans la feuille de calcul. La feuille défile à l’intérieur de ce cadre.

StartInsertFormulasDataView
A1fxPréparation de votre classeur…
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

Saisissez votre formule dans la feuille, puis vérifiez.

À propos de cet exercice

Un jeu de données de 50 lignes sur une feuille, 3 cellules de réponse, environ 8 minutes. Corrigé dans votre navigateur : la valeur et la formule qui la produit, avec des indications sur la correction à apporter.

Comment le résoudre

  • 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.

Autres méthodes valables

  • =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

Erreurs fréquentes

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.

Comment nous corrigeons

Toute formule qui produit le bon résultat est acceptée, sauf si la tâche nomme une fonction : une autre fonction rapporte alors la moitié des points. Nous comparons les valeurs avec une petite tolérance et ignorons la casse et les espaces dans le texte. Un nombre correct saisi à la main au lieu d’une formule rapporte la moitié des points. Les tâches sur plusieurs cellules rapportent tous les points avec 100 % de cellules justes, la moitié à partir de 50 %. Lorsque c’est pertinent, nous modifions aussi une cellule d’entrée sur une copie de votre feuille et vérifions que votre formule suit.

Réponses calculées selon les règles d’Excel 365 et vérifiées dans notre correcteur le 16 novembre 2026. Auteur : GridBelt team.

Dans ces programmes

  • Excel test in 7 days, Plan de niveau 2Jour 6
  • Excel for Data AnalystsJour 10
  • Excel for Sales and MarketingJour 8

Exercices associés

  • Sales by region with SUMIFDébutant, ~5 min
  • Sales by region and product with SUMIFSIntermédiaire, ~7 min
  • Average and largest deal per rep (AVERAGEIFS, MAXIFS)Intermédiaire, ~8 min
  • Total of orders above $500Débutant, ~4 min

Référence de la fonction

Documentation officielle

Exercice suivant

Highlight overdue invoices (conditional formatting)