Cellwise Start free

Excel guide · 6 min read

How to calculate backorder rate in Excel

Quick answerBackordered units per line = MAX(0, ordered - shipped in the first shipment). Unit backorder rate = backordered units / units ordered, =SUM(F2:F15)/SUM(D2:D15). Line backorder rate = lines with a backorder / all lines, and order backorder rate = orders with any backordered line / all orders. On the sample data they are 20.6%, 35.7% and 62.5%, so always say which one you report.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1OrderOrder dateSKUOrderedShipped firstBackorderedFinal ship dateDays on BOOpen ageOrder has BOReport dateNov 13, 2026
2SO-2201Nov 2, 2026SKU-A10040400TRUEEarlier report dateNov 8, 2026
3SO-2201Nov 2, 2026SKU-B220251015Nov 9, 20267TRUE
4SO-2202Nov 3, 2026SKU-A10060600FALSE
5SO-2202Nov 3, 2026SKU-C31012120FALSE
6SO-2203Nov 4, 2026SKU-B22030030Nov 12, 20268TRUE
7SO-2204Nov 5, 2026SKU-D450880FALSE
8SO-2204Nov 5, 2026SKU-C31020200FALSE
9SO-2205Nov 6, 2026SKU-C31020146Nov 10, 20264TRUE
10SO-2206Nov 9, 2026SKU-A10050500TRUE
11SO-2206Nov 9, 2026SKU-D45010464TRUE
12SO-2207Nov 10, 2026SKU-B220150153TRUE
13SO-2207Nov 10, 2026SKU-A10030330TRUE
14SO-2208Nov 11, 2026SKU-D450660FALSE
15SO-2208Nov 11, 2026SKU-A10024240FALSE

Download this sheet as an Excel practice file (.xlsx)

Backordered units on each order line

The sheet is two weeks of order lines exported from an order system: order, order date, SKU, units ordered and units in the first shipment (D and E). When the remaining units finally ship, the date goes in G; while they are still waiting, G stays blank. SKU-B220 ran short for most of the period, so it shows up again and again.

In F2, =MAX(0,D2-E2) gives the units still owed on that line. Fill it down. MAX with 0 matters on row 13: SO-2207 ordered 30 of SKU-A100 and the warehouse sent 33. Without MAX that line would show -3 and cancel out 3 units of real backorders elsewhere.

Don't try to do the whole column in one go with =MAX(0,D2:D15-E2:E15). MAX returns one number for the whole range, the single biggest shortfall (30 here), not a value per row. Fill the row formula down, or use the SUMPRODUCT version in the next section.

=SUM(F2:F15)

Result: 72

72 units on backorder across the 14 lines: 15 + 30 + 6 + 6 + 15.

Unit backorder rate, and the over-shipment trap

Divide backordered units by units ordered. The quick version people type first, =(SUM(D2:D15)-SUM(E2:E15))/SUM(D2:D15), says 19.7%: it lets the 3 extra units on SO-2207 cancel 3 units that customers are still waiting for. The per-line MAX in column F gives the right answer.

Without a helper column, the formula below does the same in one cell and works in any Excel version: (D2:D15>E2:E15) is 1 only on short lines, so over-shipped lines add nothing.

If every shortfall is backordered, the unit backorder rate is 1 minus the unit order fill rate (with over-shipments capped): 79.4% filled, 20.6% backordered. The two split apart when customers cancel the missing units. A cancelled shortfall is a lost sale, not a backorder, so give cancellations their own column and keep them out of F.

=SUMPRODUCT((D2:D15>E2:E15)*(D2:D15-E2:E15))/SUM(D2:D15)

Result: 0.20571428571 shows as 20.6% with Percent format

0.206, a 20.6% unit backorder rate: 72 of the 350 units ordered went on backorder.

Backorder rate by lines and by orders

Units hide small misses behind big orders. Count lines instead and each short line counts once, whatever the quantity: =COUNTIF(F2:F15,">0")/ROWS(F2:F15) is 5 of 14 lines, 35.7%.

The order rate is what customers feel: an order with one backordered line arrives in two boxes. Each order sits on several rows, so count unique order IDs. The formula below shows all three rates side by side; the third part keeps the IDs of lines with a backorder, removes repeats with UNIQUE and divides by the number of unique orders (Excel 365 and 2021).

In older Excel, use helper column J: =COUNTIFS($A$2:$A$15,A2,$F$2:$F$15,">0")>0 marks every line of an order that has any backorder. Then =SUMPRODUCT(J2:J15/COUNTIF(A2:A15,A2:A15))/SUMPRODUCT(1/COUNTIF(A2:A15,A2:A15)) weights each line by 1 / its order's line count, so each order counts once: 62.5% again. Weighting column F directly instead of J gives 43.8%, because an order with one short line out of two would count as half an order.

=HSTACK(SUM(F2:F15)/SUM(D2:D15),COUNTIF(F2:F15,">0")/ROWS(F2:F15),COUNTA(UNIQUE(FILTER(A2:A15,F2:F15>0)))/COUNTA(UNIQUE(A2:A15)))

Result: spills 1 row × 3 columns

0.205714285710.357142857140.625

20.6% of units, 35.7% of lines and 62.5% of orders: 5 of the 8 orders had at least one line backordered.

Open backorders on a report date

The rate tells you how often it happens. The open backlog tells you what is still owed today. A line is open on a report date when it was ordered on or before that date and its final ship date is blank or later than the date. That is two SUMIFS added together: one for blank ship dates ("" as the criteria matches empty cells) and one for ship dates after the report date.

With the report date in L1 (13 November), 21 units are still open: 6 of SKU-D450 on SO-2206 and 15 of SKU-B220 on SO-2207. Point the same formula at L2 (8 November) and it says 51 units on 3 lines, the backlog as it stood that day, because the lines filled on 9, 10 and 12 November were still waiting then. A line filled on the report date itself counts as closed.

Swap SUMIFS for COUNTIFS with F2:F15,">0" as an extra condition to count open lines instead of units. Keep the report date in a cell rather than using TODAY(): a weekly report should still say the same thing when you open it next month.

=SUMIFS(F2:F15,B2:B15,"<="&L1,G2:G15,"")+SUMIFS(F2:F15,B2:B15,"<="&L1,G2:G15,">"&L1)

Result: 21

21 backordered units still open on 13 November.

Average days on backorder (and why open lines matter)

How long customers wait is often a better measure than how often. In H2, =IF(G2="","",G2-B2) gives the days from order date to final shipment for lines that have been filled. If the result shows as a date, set the column's number format to General. The formula below averages only rows with a final ship date.

Closed lines took 7, 8 and 4 days. But the average leaves out the two lines still waiting, and they get older every day. Column I tracks them: =IF(AND(F2>0,G2=""),$L$1-B2,"") is the age of each open backorder on the report date, and =MAXIFS(I2:I15,F2:F15,">0") shows the oldest, 4 days. Report the closed average and the oldest open age together. Averaging closed and open ages into one number (5.2 days here) makes the wait look shorter than it is.

Measuring from the first shipment date instead of the order date shows how long the backordered units themselves waited. Pick one, write it in the report heading and keep it.

=AVERAGEIFS(H2:H15,G2:G15,"<>")

Result: 6.3333333333 shows as 6.3 with Number format

6.3 days on average from order to final shipment, for the 3 backordered lines that have been filled.

Which SKUs cause the backorders

List each SKU with its backordered units and its own backorder rate, biggest first. UNIQUE builds the SKU list, SUMIFS takes that list as its criteria so it returns one total per SKU, and SORTBY sorts by units and then by rate (Excel 365).

SKU-B220 accounts for 60 of the 72 backordered units and missed 85.7% of what was ordered: a stock problem, not bad luck. Check its safety stock and reorder point, the supplier's real lead time and how many days it was out of stock with the stockout rate. SKU-D450 and SKU-C310 have 6 units each; D450 ranks higher because 6 of 24 is a bigger share than 6 of 52.

=LET(s,UNIQUE(C2:C15),b,SUMIFS(F2:F15,C2:C15,s),o,SUMIFS(D2:D15,C2:C15,s),SORTBY(HSTACK(s,b,b/o),b,-1,b/o,-1))

Result: spills 4 rows × 3 columns

SKU-B220600.85714285714
SKU-D45060.25
SKU-C31060.11538461538
SKU-A10000

SKU-B220 60 units (85.7%), SKU-D450 6 (25%), SKU-C310 6 (11.5%), SKU-A100 0 (0%).

Common mistakes

Subtracting total shipped from total ordered

Over-shipped lines cancel real shortfalls (19.7% instead of 20.6% here). Use MAX(0, ordered - shipped) on each line, or SUMPRODUCT with (ordered>shipped).

Counting cancelled shortfalls as backorders

If the customer cancels the missing units, nothing is owed any more. Record cancellations in their own column so the backorder rate and the open backlog stay honest.

Overwriting the first-shipment quantity when the backorder ships

If E becomes the total shipped, every filled backorder disappears from the history and last month's rate keeps falling. Keep the first shipment in E and the final ship date in G.

Reporting average days on backorder without the open lines

Lines still waiting are left out of the average. Show the oldest open backorder (MAXIFS on the open-age column) next to it.

FAQ

What is the formula for backorder rate?

Backordered units / units ordered for the same period. In Excel: =SUMPRODUCT((D2:D15>E2:E15)*(D2:D15-E2:E15))/SUM(D2:D15), with units ordered in D and units shipped in the first shipment in E. Use lines or orders instead of units for the line and order backorder rates.

Is backorder rate the same as 1 minus fill rate?

Only when every shortfall is backordered and over-shipments are capped. When customers cancel missing units, those units lower the fill rate but are not backorders, so the two no longer add up to 100%.

How do I count open backorders in Excel?

Sum the backordered units where the order date is on or before the report date and the final ship date is blank or after it: two SUMIFS added together, one with "" as the ship-date criteria and one with ">"&report_date.

How do I calculate days on backorder?

Subtract the order date from the date the backordered units shipped: =G2-B2, and average the filled lines with AVERAGEIFS. For lines still open, subtract the order date from the report date to get their age.

What is a good backorder rate?

There is no universal number: it depends on the product, the season and what customers accept. Track your own trend by week or month, by SKU, and against the fill rate target you have agreed with sales.

Functions used

Related guides

Updated