📌 Stage 3 · Production and Stock · Inventory & COGS · Stock on hand · 30-40 min

SUMIFS across journals, lifetime counts, and the shrink bridge

Learning goals

  • Count completed production per SKU with a two-condition SUMIFS
  • Count lifetime sales per SKU with SUMIF
  • Build the on-hand bridge: produced minus sold plus net shrink

Concepts

Two counts, one filter each

Units produced must respect the status column: SUMIFS adds the quantity where the SKU matches and the status is exactly Completed, so BATCH-508's 70 planned jars are not stock yet.

Units sold is a plain SUMIF over the Sales journal with no date filter, because a physical count is lifetime: every order ever shipped has left the building, including the Oct 2 order E-1011 that period summaries exclude. JG-01 shows 90 sold even though September reports say 82.

=SUMIFS(Production!$F$5:$F$1004,Production!$D$5:$D$1004,$B5,Production!$I$5:$I$1004,"Completed")

Units produced: sum Production quantity where the SKU matches this row and status is Completed. JG-01 returns 140 (60 plus 80).

=$I5-$J5+$K5

On-hand bridge: produced minus sold plus net shrink. For JG-01: 140 - 90 + (-3) = 47 jars on the shelf.

💡 Tips:

  • SUMIF takes (range, criteria, sum range); SUMIFS takes (sum range, criteria range, criteria, ...). Mixing up the argument order is the most common bug in both.

The shrink column is a bridge, not a fudge

Column K pulls the net variance from the Cycle Counts sheet (Lesson 16). Cracked jars count negative; a restocked customer return counts positive. Adding K keeps on hand equal to what a physical count finds.

Without the bridge, every count variance would silently widen the gap between the sheet and the shelf, and the reorder math of Lesson 17 would trigger on numbers nobody believes.

Practice

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

  1. Check the counts — For JG-01, confirm I5 (produced) is 140, J5 (sold) is 90, and K5 (shrink) is -3. Note J5 includes the Oct 2 order E-1011.
  2. Type the bridge — Cell L5 is blank. Type =$I5-$J5+$K5 and press Enter. JG-01 lands on 47 units on hand.
  3. Fill down — Fill L5 down to L9. On hand reads 47, 64, 17, 13, 13. JG-04 shows 13 because a customer return added one back.
  4. Reconcile against batches — Open Production and confirm the JG-01 layers: BATCH-501 has 0 left, BATCH-504 has 50 left, and 0 plus 50 equals the 47 on hand plus the 3 cracked jars.

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 =$I5-$J5+$K5 into SKUs!L5 and got 47 for JG-01
  • ☐ Confirmed lifetime sales of 90 for JG-01 versus 82 in the September period
  • ☐ Reconciled layer leftovers (0 + 50) against on hand plus shrink (47 + 3)
  • ☐ Verified the baseline: on hand 47 / 64 / 17 / 13 / 13 across the five SKUs