📌 Stage 5 · Margin Analysis · Inventory & COGS · Margin matrix · 35-45 min

A SUMIFS matrix that filters SKU, channel, and period in one formula

Learning goals

  • Build a SKU-by-channel margin table with SUMIFS and COUNTIFS
  • Filter every figure to the Settings period with date criteria
  • Read the TOTAL row as the September profit and loss

Concepts

One row per SKU and channel pair

The Margins sheet lists the ten combinations that actually sold in September: five SKUs across Etsy and Shopify. Each row aggregates the Sales journal three ways: how many orders, how many units, how much net revenue; then the FIFO COGS sheet once, for cost.

Every formula carries the same two date conditions built from Settings, which is what makes the whole table flip when the period changes.

=SUMIFS(Sales!$G$5:$G$1004,Sales!$E$5:$E$1004,$B5,Sales!$D$5:$D$1004,$D5,Sales!$C$5:$C$1004,">="&Settings!$C$5,Sales!$C$5:$C$1004,"<"&(Settings!$C$6+1))

Units sold for this SKU on this channel in period: sum Sales qty where SKU matches, channel matches, and the date is on or after the period start and strictly before the day after the period end. The ampersand builds each comparison from the Settings dates.

=SUMIFS('FIFO COGS'!$I$5:$I$1004,'FIFO COGS'!$E$5:$E$1004,$B5,'FIFO COGS'!$D$5:$D$1004,$D5,'FIFO COGS'!$C$5:$C$1004,">="&Settings!$C$5,'FIFO COGS'!$C$5:$C$1004,"<"&(Settings!$C$6+1))

FIFO COGS for the same pair and period, summed from the draw sheet. The draw sheet carries its own date column (looked up per draw), so the Oct 2 draw is excluded here even though it drained a layer.

=IF($G5=0,0,$I5/$G5)

Margin percent per pair: gross profit over net revenue, guarded against zero.

💡 Tips:

  • "<"&(Settings!$C$6+1) reads as strictly before Oct 1, 2026, which includes every moment of Sep 30 without touching Oct 1. It is the standard inclusive-end pattern for date criteria.

The TOTAL row is the month

Row 15 sums the ten pairs: 17 orders, 161 units, $4,863.60 net revenue, $757.17 FIFO COGS, $4,106.43 gross profit, 84.4 percent. Because every figure is period-filtered at the source, the row is a real September gross P and L, not a ledger total that happens to include October.

Practice

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

  1. Read the matrix — Ten rows from JG-01 on Etsy down to JG-03 on Shopify. Each shows orders, units, net revenue, FIFO COGS, profit, and margin percent.
  2. Type the net-revenue SUMIFS — Cell G5 is blank. Type =SUMIFS(Sales!$J$5:$J$1004,Sales!$E$5:$E$1004,$B5,Sales!$D$5:$D$1004,$D5,Sales!$C$5:$C$1004,">="&Settings!$C$5,Sales!$C$5:$C$1004,"<"&(Settings!$C$6+1)) and press Enter. JG-01 on Etsy returns $832.50.
  3. Check one pair by hand — JG-01 on Etsy is orders E-1001, E-1003, E-1007: nets of $146.50, $293.90, $392.10 sum to $832.50. FIFO COGS $120.34 leaves $712.16 profit.
  4. Verify the month — Row 15 (TOTAL) reads 17 orders, 161 units, $4,863.60 net, $757.17 COGS, $4,106.43 profit, 84.4 percent. The Oct 2 order appears nowhere on this sheet.

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 SUMIFS into Margins!G5 and got $832.50 for JG-01 on Etsy
  • ☐ Reconciled that pair by hand against the three Etsy JG-01 orders
  • ☐ Confirmed the TOTAL row: 17 orders, 161 units, $4,106.43 profit at 84.4 percent
  • ☐ Verified the baseline: September net revenue $4,863.60, FIFO COGS $757.17