How to calculate sell-through rate in Excel

Step 1: Units sold per product from the weekly log

The sheet is a shop's spring drop after three weeks. I:K is the sales log, one row per product per week, the way most point-of-sale exports look. A:H is the summary you build from it.

Total each product's sales with SUMIFS so the summary updates when you paste in next week's rows. In D2, copied down: =SUMIFS($K$2:$K$13,$J$2:$J$13,A2). The Linen shirt sold 88, the Denim jacket 24, the Canvas tote 61 and the Rain shell 9.

The product names in A must match the log exactly. A trailing space in "Rain shell " makes SUMIFS return 0 and the product looks like it sold nothing.

Step 2: Sell-through rate

Divide units sold by units you had to sell: =D2/(B2+C2) in E2, formatted as a percentage. Linen shirt 73.3%, Denim jacket 33.3%, Canvas tote 61.0%, Rain shell 18.0%.

Sell-through is a share of stock, so it only ever means something for a stated period. "33% in three weeks" is useful; "33%" alone isn't. Keep the period in the header or in a setting cell, as N3 does here (=MAX(I2:I13) picks up the latest week in the log).

Received only, or opening stock plus received?

Both versions are in use, and they give different answers when a product had leftover stock. Units sold / units received is common for a single delivery or a new drop. Units sold / (opening stock + received) is the fairer one when old stock is on the same shelf.

The Denim jacket shows the gap. On received only it's =D3/C3, exactly 40%, which meets the 40% target in N2. Counting the 12 jackets left from last season, it's 33.3% and falls short. Those 12 jackets were sitting on the shelf too, so leaving them out flatters the product.

Pick one definition, write it in a note next to the table and use it for every product and every week. Switching between them is how two reports on the same sales end up disagreeing.

Sell-through by a given week

To see how fast a drop is moving, add a week condition to SUMIFS. Sell-through for the Linen shirt by the end of week 2: =SUMIFS($K$2:$K$13,$J$2:$J$13,A2,$I$2:$I$13,"<=2")/(B2+C2), 52.5%. By week 3 it's 73.3%.

The same pattern works for a season or a drop. Add a Drop or Season column to the log and a matching criteria pair, such as $L$2:$L$13,"Spring". Then one table can compare drops at the same week, which is a fairer test than comparing a drop that has been out for three weeks with one that has been out for ten.

Weeks of stock left, and flagging slow sellers

On hand is =B2+C2-D2. Weeks of stock at the current rate is on hand divided by the average weekly sales: =F2/(D2/$N$3). The Linen shirt has 1.09 weeks left, the Canvas tote 1.92, the Denim jacket 6.00 and the Rain shell 13.67.

The three-week average can hide a slowdown. The Rain shell sold 4, then 3, then 2. At last week's rate its 41 units last =F5/K13, 20.5 weeks. If sales are trending, use the latest week or the last two, and say which.

For the flag, put the target in N2 so a buyer can change it without touching formulas: =IF(E2<$N$2,"Slow","On track"). The Denim jacket and the Rain shell show Slow. Which action follows (a markdown, a move to another store, or waiting for the weather) is a judgement call; the flag just tells you where to look first.