Low-stock alert and reorder quantity in Excel and Google Sheets
Flag items at or below their reorder level, work out how many to order, and count the items to reorder.
The problem
You need to know which items to reorder, and how many.
Sample data
| Item (A) | Stock (B) | Reorder level (C) | Target (D) |
|---|---|---|---|
| Bolts | 20 | 25 | 100 |
| Nuts | 60 | 30 | 100 |
| Washers | 30 | 30 | 80 |
| Screws | 0 | 10 | 50 |
Formulas
Alert E2: =IF(B2<=C2,"Reorder","OK") -> Reorder, OK, Reorder, Reorder
Qty to order F2: =IF(B2<=C2,D2-B2,0) -> 80, 0, 50, 50
Items to reorder: =SUMPRODUCT(--(B2:B5<=C2:C5)) -> 3
Stock equal to the reorder level counts as reorder (Washers).
Do not use COUNTIF for the count
COUNTIF cannot compare each row against its own level. SUMPRODUCT can.
Excel vs Sheets
Same in both.
Tested on sample data by the Cellwise checker (log CW-PL-20261021-61). AI-made: the text, voice and visuals are generated by AI.
Checked before publishing: a check.