Excel guide · 5 min read
How to calculate cost of goods sold in Excel
=F2+G2-H2. Divide COGS by sales for COGS %, and 1 minus that is your gross margin.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Category | Amount | Category | Beginning inventory | Purchases | Ending inventory | COGS | Sales | COGS % | Gross margin % | |
| 2 | Jul 3, 2026 | Bikes | 8400 | Bikes | 22000 | 20500 | 19800 | 22700 | 34500 | 0.65797101449 | 0.34202898551 | |
| 3 | Jul 10, 2026 | Parts | 1250 | Parts | 4800 | 3770 | 5100 | 3470 | 7900 | 0.43924050633 | 0.56075949367 | |
| 4 | Jul 22, 2026 | Accessories | 960 | Accessories | 3100 | 1700 | 2650 | 2150 | 3950 | 0.54430379747 | 0.45569620253 | |
| 5 | Aug 5, 2026 | Bikes | 6200 | Clothing | 2600 | 1480 | 3300 | 780 | 1690 | 0.46153846154 | 0.53846153846 | |
| 6 | Aug 14, 2026 | Clothing | 1480 | Total | 32500 | 27450 | 30850 | 29100 | 48040 | 0.60574521232 | 0.39425478768 | |
| 7 | Aug 19, 2026 | Parts | 1420 | |||||||||
| 8 | Sep 2, 2026 | Accessories | 740 | |||||||||
| 9 | Sep 11, 2026 | Bikes | 5900 | |||||||||
| 10 | Sep 25, 2026 | Parts | 1100 | |||||||||
| 11 | Oct 2, 2026 | Bikes | 7300 |
Download this sheet as an Excel practice file (.xlsx)
The COGS formula and the layout
COGS is what the stock you sold cost you. Most small shops don't track the cost of every sale, so they work it out for a period: what you had at the start, plus what you bought, minus what's still on the shelves at the end. Whatever disappeared was sold (or lost, which matters later).
The sample is a bike shop's third quarter. A:C is the purchases log (date, category, cost). E:L is one row per category: beginning inventory (last quarter's closing count), purchases for the period (a formula), ending inventory (this quarter's count, at cost), COGS, sales, COGS % and gross margin. The period dates sit in N2 and O2 so next quarter you change two cells.
Value beginning and ending inventory the same way, at cost, not at retail price (the cost per unit guide shows how to get a blended unit cost across several purchases). Mixing the two is the commonest reason a COGS figure looks wildly wrong. Which costs go into inventory (freight in, supplier discounts) is an accounting policy choice; agree it with your accountant and apply it the same way every period.
- Type the category names in E2:E5 and the opening and closing stock values in F and H.
- Put the period start in N2 and the period end in O2.
- Add the formulas from the next sections in G2 and I2:L2, then fill them down to row 5.
Purchases for the period with SUMIFS
G2 adds the log's purchases for the row's category between the two period dates. The date conditions matter: the log already holds a 7,300 bike delivery on 2 Oct, after the quarter ended. Those bikes aren't in the 30 Sep count, so counting the purchase would push Bikes COGS up by 7,300 for stock you still had.
Result: 20500
20,500 of bike purchases in July to September. Without the two date conditions it returns 27,800. In the table the ranges and period cells are locked with $ so the formula fills down.
My log has times in the dates
Then "<="&O2 misses purchases on the last day after midnight. Use "<"&O2+1 for the end condition instead.
COGS per category and in total
With purchases in place, I2 is the formula itself, =F2+G2-H2, filled down. Add a total row with SUM on each column, and check that total COGS also equals total beginning + total purchases - total ending: if the two disagree, a row's formula was overtyped.
Result: 22700
22,700 for bikes: 22,000 at the start, 20,500 bought, 19,800 left on 30 Sep. Across all four categories COGS is 29,100 on sales of 48,040.
What does negative COGS mean?
Ending inventory is worth more than everything you started with plus everything you bought, which can't happen. It means a purchase is missing from the log, the count is wrong, or the closing stock was valued at retail instead of cost. Flag it with =IF(I2<0,"Check","").
COGS % and gross margin
COGS % is COGS divided by sales for the same period, and gross margin is 1 minus that. Comparing categories shows where the money is made: bikes bring most of the sales but keep the smallest share of each sale.
Use =I6/J6 on the totals row, not an AVERAGE of the category percentages: the average gives clothing (a small category) the same weight as bikes.
Result: 0.60574521232 shows as 60.6% with Percent format
0.6057: 60.6% of the quarter's sales went on the cost of the goods, a gross margin of 39.4%. Bikes alone run at 65.8%, parts at 43.9%.
Is gross margin the same as markup?
No. Margin is profit as a share of the selling price; markup is profit as a share of cost. A 39.4% margin is about a 65% markup. See markup vs margin for the conversion.
Why the stock count moves COGS one for one
Ending inventory is subtracted, so every euro or dollar of counting error lands straight in COGS. Count too much stock and COGS (and so profit) looks better than it is; count too little and it looks worse. Say a box of parts worth 400 was counted twice: the true closing stock is 4,700, not 5,100, and parts COGS rises by exactly 400.
The same goes for theft, damage and stock written off: it isn't on the shelf at the count, so it shows up in COGS. To see how much of COGS is really loss, compare the counted stock with what your records say should be there; see inventory shrinkage.
Result: 3870
3,870 instead of 3,470: parts COGS rises by the full 400 once the double-counted box is taken out of the closing stock.
What do I do with COGS next?
It feeds gross profit on your profit and loss statement and inventory turnover (COGS / average inventory); see inventory turnover.
Common mistakes
Counting purchases outside the period
Add start and end date conditions to the SUMIFS. A delivery after the count date isn't in ending inventory, so it mustn't be in purchases either.
Valuing ending stock at retail
Beginning stock, purchases and ending stock must all be at cost. Retail values inflate ending inventory and make COGS far too low.
Averaging category percentages
Divide total COGS by total sales. An AVERAGE weights a small category the same as your biggest one.
Carrying the wrong opening figure
This period's beginning inventory must be last period's ending count. Link it (=the previous sheet's closing cell) instead of retyping it.
FAQ
What is the formula for cost of goods sold?
Beginning inventory + purchases - ending inventory, all at cost and for the same period. In Excel: =beginning+purchases-ending, with purchases from a SUMIFS on your log.
How do I calculate COGS percentage in Excel?
Divide COGS by sales for the same period, =COGS/sales, and format as a percentage. Gross margin is =1-COGS%.
Is COGS the same as total expenses?
No. COGS is only the cost of the goods you sold. Rent, wages for staff who don't make the product, advertising and software are operating expenses, below gross profit.
Can I calculate COGS without a stock count?
Only if you track the cost of every item sold (a perpetual inventory system, usually in point-of-sale software). The beginning + purchases - ending method needs a closing count or a reliable stock figure.
Functions used
Related guides
- How to calculate landed cost in Excel
- How to do FIFO inventory valuation in Excel
- How to calculate inventory turnover in Excel
- How to calculate profit margin in Excel
- How to calculate inventory shrinkage in Excel
- ABC inventory analysis in Excel
Updated