📌 Stage 1 · Foundations · Inventory & COGS · Materials and SUMIF · 30-40 min

Eight materials, their units and suppliers, and a purchased-quantity SUMIF

Learning goals

  • Keep a material list with units of measure, suppliers, and lead times
  • Use SUMIF to total everything ever purchased of one material
  • Read purchased, consumed, and on-hand quantity as one flow

Concepts

Materials, units, and lead times

Every Juniper candle is built from eight materials: soy wax in pounds, two fragrance oils in fluid ounces, jars, wicks, labels, gift boxes, and mailers in eaches. Mixed units are the classic way maker spreadsheets break, so the unit lives in master data and every quantity elsewhere means that unit.

Suppliers and lead times live here too. Fir needle oil takes 14 days from Cascadia Fragrance LLC; wicks take 7. Lesson 17 turns these numbers into reorder points for finished goods.

SUMIF: the one-line inventory roll-up

Column G totals the Purchases journal per material. SUMIF walks the purchase rows, adds the quantity where the material code matches, and ignores everything else. RM-01 soy wax has two purchase layers, 120 lb in August and 150 lb in September, so G5 returns 270 lb.

Column H does the same against the BOM sheet for quantity consumed by completed batches, and column I subtracts. Purchased minus consumed equals on hand.

=SUMIF(Purchases!$D$5:$D$1004,$B5,Purchases!$G$5:$G$1004)

Sum the Qty column of Purchases (first range) wherever the Material code (second range) equals this row's code in B5. The dollar signs pin the journal ranges so the formula fills down cleanly.

=$G5-$H5

On-hand quantity. For soy wax: 270 lb purchased minus 202.5 lb consumed leaves 67.5 lb on the shelf.

💡 Tips:

  • SUMIF and SUMIFS differ only in argument order and how many conditions they take: SUMIFS puts the sum range first and accepts pairs of criteria range and criteria.

Practice

Fixed case (Sep 2026). The exercise opens on Materials. Pale-green cells are formulas.

  1. Read the master list — Confirm the eight materials RM-01 to RM-08 with their units, suppliers, and lead times from 5 to 14 days.
  2. Type the purchased-quantity formula — Cell G5 is blank. Type =SUMIF(Purchases!$D$5:$D$1004,$B5,Purchases!$G$5:$G$1004) and press Enter. The result is 270 lb of soy wax.
  3. Fill down and check the flow — Select G5 and fill the formula down to G12. Every material shows its total purchased quantity; RM-02 fir oil returns 180 fl oz. Then read H5 (202.5 consumed) and I5 (67.5 on hand).
  4. Meet the cost columns — Columns J through L (last purchase date, latest unit cost, on-hand value) are wired in Lesson 6.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed the SUMIF into Materials!G5 and got 270 lb of soy wax
  • ☐ Confirmed fir oil purchased 180 fl oz and wax on hand 67.5 lb
  • ☐ Can explain why purchased minus consumed must equal on hand
  • ☐ Verified the baseline: 8 materials, wax 270 lb purchased and 67.5 lb on hand