Vai al contenuto
GridBelt
  • Test Excel
  • Esercizi
  • Programmi
  • Prezzi
Accedi
Inizia il test gratuito
  • Test Excel
  • Esercizi
  • Programmi
  • Prezzi
AccediCrea un account
Inizia il test gratuito
GridBelt

Esercitati con Excel nel tuo browser.

Test Excel

  • Livello 1: Principiante
  • Livello 2: Intermedio
  • Livello 3: Esperto
  • Test Excel in 7 giorni
  • Classifica
  • Test Excel per il colloquio
  • Valutazione Excel di Indeed

Esercizi

  • Tutti gli esercizi
  • Programmi
  • Per principianti

Guide

  • Formule Excel
  • Scorciatoie Excel

Azienda

  • Chi siamo
  • Prezzi
  • Come valutiamo

© GridBelt. Tutti i diritti riservati.

  • Informativa sulla privacy
  • Termini di utilizzo
  • Politica di rimborso

Excel è un marchio di Microsoft Corporation. Indeed, TestGorilla, SHL e gli altri marchi appartengono ai rispettivi proprietari. GridBelt è un sito di esercitazione indipendente e non è affiliato a Microsoft, TestGorilla, SHL o Indeed, né approvato o sponsorizzato da queste società.

  1. Esercizi
  2. /
  3. SUMIF and SUMIFS

SUMPRODUCT practice: weighted average price over 50 purchase lots

  • Esperto
  • ~8 min
  • SUMIF and SUMIFS

Testo dell’esercizio in inglese

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.

Cosa devi fare

  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.

Celle di risposta

Correzione

Scrivi la formula nella griglia, premi Enter, poi Verifica. Valutiamo il valore in ogni cella di risposta e la formula che lo produce.

Attività

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

Consigliato il computer per le attività sul foglio di calcolo. Il foglio scorre all’interno di questo riquadro.

StartInsertFormulasDataView
A1fxPreparazione della cartella di lavoro…
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

Inserisci la formula nel foglio, poi verifica.

Informazioni sull’esercizio

Un set di dati di 50 righe su un foglio, 3 celle di risposta, circa 8 minuti. Correzione nel tuo browser: il valore e la formula che lo produce, con indicazioni su cosa sistemare.

Come risolverlo

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

Altri metodi validi

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

Errori comuni

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.

Come valutiamo

È accettata qualsiasi formula che produce il risultato giusto, a meno che l’attività non indichi una funzione: in quel caso un’altra funzione vale metà dei punti. Confrontiamo i valori con una piccola tolleranza e ignoriamo maiuscole, minuscole e spazi nel testo. Un numero corretto digitato a mano invece di una formula vale metà dei punti. Le attività su più celle valgono il punteggio pieno con il 100% delle celle giuste, metà dei punti dal 50%. Dove serve, cambiamo anche una cella di input su una copia del tuo foglio e controlliamo che la formula si aggiorni.

Risposte calcolate con le regole di Excel 365 e verificate nel nostro correttore in data 16 novembre 2026. Autore: GridBelt team.

In questi programmi

  • Excel test in 7 days, Piano di livello 2Giorno 6
  • Excel for Data AnalystsGiorno 10
  • Excel for Sales and MarketingGiorno 8

Esercizi correlati

  • 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

Guida alla funzione

Documentazione ufficiale

Esercizio successivo

Highlight overdue invoices (conditional formatting)