The Case, five steps guide
A stock count, a list of purchase orders in every state, and a reorder point per item. Work out what's really on its way, which items need reordering, and which supplier has the most of them. Received and cancelled orders are in the list to catch you out.
What counts as on order
Only open orders are still coming. A received order is already in the stock count, and counting it again makes the shelf look fuller than it is. A cancelled order won't arrive at all. And an item can have two open orders, which a lookup would stop at the first of.
Step by step
- On order.
=SUMIFS(Orders!$C$2:$C$16, Orders!$B$2:$B$16, A2, Orders!$D$2:$D$16, "Open"). SUMIFS adds every matching order and gives 0 when there is none. See SUMIFS. - Available.
=C2+F2: on hand plus on order. - Reorder?
=IF(G2<=D2, "Reorder", ""). At the reorder point is the moment to order, so the test includes it:<=. - Per supplier.
=COUNTIFS($E$2:$E$41, J2, $H$2:$H$41, "Reorder"), ranges locked. - The answer.
=INDEX($J$2:$J$5, MATCH(MAX($K$2:$K$5), $K$2:$K$5, 0)).
Where it goes wrong
- SUMIF without the status. Received and cancelled orders count as stock on the way.
- XLOOKUP or VLOOKUP for the quantity. An item with two open orders gets only the first.
- < instead of <=. Items sitting exactly at their reorder point get skipped.
- The biggest supplier instead of the one with the most reorders. The supplier with the most items isn't necessarily the one with the most to reorder.
Check it once by hand
Pick one item with two orders, filter the Orders sheet to it, and add the open ones yourself. Then filter the Action column to Reorder and compare the row count with the supplier totals.
FAQ
- How do I make a reorder list in Excel?
- Add stock on hand and open orders, then flag each item with IF(available<=reorder_point,"Reorder",""). Count the flags per supplier with COUNTIFS.
- How do I sum orders with a condition on their status?
- Use SUMIFS with two conditions: the item and the status, for example =SUMIFS(qty, sku, A2, status, "Open").
- Why does my lookup return only one order?
- XLOOKUP and VLOOKUP stop at the first match. To add up every order for an item, use SUMIF or SUMIFS.
- Should the reorder test use < or <=?
- Usually <=: the reorder point is the level at which you order. Check how your team defines it.
