Skip to content
GridBelt
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign in
Start the free test
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign inCreate account
Start the free test
GridBelt

Practice Excel in your browser.

Excel Test

  • Level 1: Beginner
  • Level 2: Intermediate
  • Level 3: Expert
  • Excel test in 7 days
  • Leaderboard
  • Excel test for interview
  • Indeed Excel assessment

Practice

  • All exercises
  • Programs
  • For beginners

Guides

  • Excel formulas
  • Excel shortcuts

Company

  • About
  • Pricing
  • How we grade

© GridBelt. All rights reserved.

  • Privacy Policy
  • Terms of Use
  • Refund Policy

Excel is a trademark of Microsoft Corporation. Indeed, TestGorilla, SHL and other marks are the property of their owners. GridBelt is an independent practice site and is not affiliated with, endorsed by or sponsored by Microsoft, TestGorilla, SHL or Indeed.

  1. Practice
  2. /
  3. SUMIF and SUMIFS

SUMPRODUCT practice: weighted average price over 50 purchase lots

  • Expert
  • ~8 min
  • SUMIF and SUMIFS

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.

What you need to do

  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.

Answer cells

Feedback

Type your formula in the grid, press Enter, then Check. We grade the value in each answer cell and the formula behind it.

Tasks

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

Desktop recommended for spreadsheet tasks. The sheet scrolls inside this box.

StartInsertFormulasDataView
A1fxPreparing your workbook…
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

Enter your formula in the sheet, then check.

About this exercise

A 50-row dataset on one sheet, 3 answer cells, about 8 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

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

Other ways that work

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

Common mistakes

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.

How we grade

Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.

Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.

In these programs

  • Excel test in 7 days, Level 2 planDay 6
  • Excel for Data AnalystsDay 10
  • Excel for Sales and MarketingDay 8

Related exercises

  • Sales by region with SUMIFBeginner, ~5 min
  • Sales by region and product with SUMIFSIntermediate, ~7 min
  • Average and largest deal per rep (AVERAGEIFS, MAXIFS)Intermediate, ~8 min
  • Total of orders above $500Beginner, ~4 min

Function reference

Official documentation

Next exercise

Highlight overdue invoices (conditional formatting)