Excel guide · 4 min read
How to calculate stockout rate in Excel
=COUNTIFS(B2:B19,"BLND-01",C2:C19,0)/COUNTIF(B2:B19,"BLND-01"). The in-stock rate is 1 minus the stockout rate.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 |
|---|---|---|---|---|---|---|---|
| 1 | Date | SKU | On hand (close) | Units sold | Report date | SKU | |
| 2 | Nov 2, 2026 | BLND-01 | 6 | 3 | Nov 7, 2026 | FLT-10 | |
| 3 | Nov 2, 2026 | FLT-10 | 40 | 12 | |||
| 4 | Nov 2, 2026 | MUG-SET | 20 | 2 | |||
| 5 | Nov 3, 2026 | BLND-01 | 3 | 3 | |||
| 6 | Nov 3, 2026 | FLT-10 | 28 | 12 | |||
| 7 | Nov 3, 2026 | MUG-SET | 18 | 2 | |||
| 8 | Nov 4, 2026 | BLND-01 | 0 | 3 | |||
| 9 | Nov 4, 2026 | FLT-10 | 15 | 13 | |||
| 10 | Nov 4, 2026 | MUG-SET | 18 | 0 | |||
| 11 | Nov 5, 2026 | BLND-01 | 0 | 0 | |||
| 12 | Nov 5, 2026 | FLT-10 | 4 | 11 | |||
| 13 | Nov 5, 2026 | MUG-SET | 15 | 3 | |||
| 14 | Nov 6, 2026 | BLND-01 | 8 | 2 | |||
| 15 | Nov 6, 2026 | FLT-10 | 0 | 4 | |||
| 16 | Nov 6, 2026 | MUG-SET | 14 | 1 | |||
| 17 | Nov 7, 2026 | BLND-01 | 5 | 3 | |||
| 18 | Nov 7, 2026 | FLT-10 | 0 | 0 | |||
| 19 | Nov 7, 2026 | MUG-SET | 12 | 2 |
Download this sheet as an Excel practice file (.xlsx)
Start from a daily stock snapshot
You need one row per SKU per day with the stock on hand, which is what most inventory and POS systems export as a daily stock report. The sample tracks three SKUs over six days (2 to 7 November): closing stock in C and units sold in D.
Decide what counts as a stockout before you build anything. Here it's closing stock of zero. Some teams use opening stock instead (empty shelf when the store opens), or stock below a minimum display quantity. Any of these works; mixing them between reports doesn't. If you only keep an in and out log, a running balance per SKU gives you the closing stock for each day: see Excel formulas for inventory.
Stockout days and stockout rate per SKU
COUNTIFS counts the rows where the SKU matches and stock is zero. Divide by the days tracked for that SKU, which COUNTIF gives you, and format as a percentage. The blender was out on 2 of 6 days: 33.3%.
Use "<=0" instead of 0 if your system ever shows negative stock. Negative on hand is a data problem (sales recorded before the receipt was booked), but on the shelf it means nothing was there, and a criterion of 0 would miss it.
For every SKU at once in Excel 365, put =UNIQUE(B2:B19) in one cell and =COUNTIFS(B2:B19,UNIQUE(B2:B19),C2:C19,"<=0")/COUNTIF(B2:B19,UNIQUE(B2:B19)) beside it. BLND-01 and FLT-10 both show 33.3%, MUG-SET 0%.
Result: 0.33333333333 shows as 33.3% with Percent format
0.333: the blender closed with no stock on 2 of the 6 days tracked.
In-stock rate across the whole range
For a single number for a store or category, count every SKU-day with stock and divide by all SKU-days. Here 14 of 18 SKU-days had stock: 77.8% in stock, 22.2% out.
This is a rate per SKU-day, so a slow item that's out for a week counts as much as a best-seller that's out for a week. If what you care about is lost revenue rather than shelf availability, weight by sales instead (next sections).
Watch for missing days. COUNTIF(C2:C19,0) does not count empty cells, but a test like C2=0 is TRUE for an empty cell, so a SUMPRODUCT version counts days with no snapshot as stockouts. Either fill the gaps or make sure both formulas see the same rows.
Result: 0.77777777778 shows as 77.8% with Percent format
0.778: 14 of 18 SKU-days had stock on hand at close.
Which items are out of stock on the report date
The question people ask every morning is "what's out right now?" Put the report date in F2 and FILTER the SKU column by date and zero stock. It spills one SKU per row, or says None.
In Excel 2019 or earlier, add a helper column with =IF(AND(A2=$F$2,C2<=0),"Out","") and filter on it. To see how long an item has been out, find the last date it had stock with =MAXIFS(A2:A19,B2:B19,"FLT-10",C2:C19,">0"): the filters were last in stock on Thu 5 Nov.
Result: FLT-10
FLT-10: the filter pack is the only SKU at zero on 7 November.
Estimate lost sales from stockout days
A rough estimate of lost sales is stockout days x average daily sales on in-stock days. AVERAGEIFS gives the second part: the filter pack sells 12 a day when it's in stock.
Be careful which days you multiply. The filters closed at zero on 6 November, but they still sold 4 that day before running out. Count that as a full lost day and you'd estimate 24 lost units. Counting only days with no stock and no sales, =COUNTIFS(B2:B19,"FLT-10",C2:C19,0,D2:D19,0), gives 1 day and 12 units, which is closer to the truth. A day that sold out still lost some sales; how much depends on what time it ran out, which a daily snapshot can't tell you.
Treat the result as an order of magnitude to prioritize which items to fix, and set the reorder point with safety stock so it happens less.
Result: 12
12: one full day out of stock at 12 units a day for the filter pack in G2.
Common mistakes
Counting only = 0
Negative stock means the shelf was empty too. Use "<=0" in COUNTIFS so system errors don't hide stockouts.
Missing snapshot days counted as stockouts
C2=0 is TRUE for an empty cell. Fill gaps in the snapshot, or use COUNTIFS, which skips empty cells.
Treating a sell-out day as a full lost day
The item sold until it ran out. Count days with zero stock and zero sales for lost-sales estimates.
Averaging in the stockout days
Average daily sales only on in-stock days (AVERAGEIFS with stock >0); zero-sales stockout days drag demand down.
FAQ
What is the formula for stockout rate?
Stockout rate = days out of stock / days tracked. In Excel: =COUNTIFS(SKU_range,"BLND-01",stock_range,"<=0")/COUNTIF(SKU_range,"BLND-01").
Is stockout rate the same as fill rate?
No. Stockout rate measures time with no stock; order fill rate measures the share of ordered units you shipped. An item can be in stock most days and still short big orders, and the units you couldn't ship become backorders.
How do I calculate the in-stock rate in Excel?
Count the SKU-days with stock above zero and divide by all SKU-days: =COUNTIF(C2:C19,">0")/COUNT(C2:C19). It's 1 minus the stockout rate when both use the same rows.
Can I calculate stockouts without daily stock levels?
Only roughly. With just weekly counts, you can see that stock hit zero but not for how many days. A daily snapshot, even exported once a day at close, is enough for these formulas.
Functions used
COUNTIFSCounts with several conditionsCOUNTIFCounts cells that matchCOUNTCounts numbersAVERAGEIFSAverages with several conditionsMAXIFSLargest value that matchesFILTERKeeps matching rows, spillsUNIQUERemoves duplicates, spillsIFPicks a result from a testANDTrue if all tests are true
Related guides
- How to calculate safety stock and reorder point in Excel
- How to calculate days of inventory on hand in Excel
- How to calculate order fill rate in Excel
- How to calculate backorder rate in Excel
- Excel formulas for inventory: stock balance, reorder and value
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate candidate pass-through rate by interviewer in Excel
Updated