Excel Challenge 23: Highlight Low Stock Items

Challenge Scenario

In retail store operations and warehouse inventory management, products are stored across two locations: the front sales shelves and the back warehouse storage room.

To avoid empty shelves and missed customer sales, inventory managers enforce dual replenishment thresholds:

Shelf Threshold: If Shelf Stock is less than 5 units (B2 < 5), the item needs replenishment.
Warehouse Threshold: If Back Warehouse Stock is less than 10 units (C2 < 10), the item needs reordering from suppliers.
Replenish Status (Column D): If either location is low (OR(B2 < 5, C2 < 10)), flag as "REORDER". Otherwise, mark as "OK".
Summary Audit (Rows 9 & 10): Count total SKUs using COUNTA in cell B9 and count flagged reorder items in cell B10 using COUNTIF.

Your Sheet Layout

Before writing formulas, let's look at how the two-location stock sheet is structured:

Inventory Stock Levels (Columns A to C): Lists SKU item codes alongside current quantities on front shelves (Column B) and back warehouse bins (Column C).
Restock Flag (Column D): Evaluates whether either storage location is below its respective safety minimum.
Inventory Summary (Rows 8 to 10): Audit block summarizing total catalog count and how many items need immediate reorder action.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:C6`	SKU, Shelf Stock, Warehouse Stock	Stock Levels	Physical item counts in two locations	Read-only physical inventory counts.
`D2:D6`	Status	Alert Status	`=IF(OR(B2 < 5, C2 < 10), "REORDER", "OK")`	Flags items if shelf < 5 OR warehouse < 10.
`B9`	Total SKUs	Audit Summary	`=COUNTA(A2:A6)`	Counts total inventory SKU items (5).
`B10`	Needs Reorder	Audit Summary	`=COUNTIF(D2:D6, "REORDER")`	Counts how many items require reordering (4).

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: D2-D6

In Column D (D2:D6), assign replenish status using =IF(OR(B2<5, C2<10), "REORDER", "OK").

Objective #2

Cell: B9

In cell B9, count total SKUs using COUNTA(A2:A6).

Objective #3

Cell: B10

In cell B10, count items needing reorder using COUNTIF(D2:D6, "REORDER").

Solution & Formula Breakdown

Let's examine how the logical IF and OR functions combine to check multiple inventory conditions:

1. Combining Multiple Conditions with OR (D2:D6)

=IF(OR(B2 < 5, C2 < 10), "REORDER", "OK")

The OR(condition1, condition2) function evaluates to TRUE if any condition is met:

Row 2 (SKU-001): Shelf = 3 (< 5), Warehouse = 12. Since shelf is below 5, OR is TRUE → "REORDER".
Row 3 (SKU-002): Shelf = 15, Warehouse = 4 (< 10). Since warehouse is below 10, OR is TRUE → "REORDER".
Row 4 (SKU-003): Shelf = 2 (< 5), Warehouse = 8 (< 10). Both are below thresholds → "REORDER".
Row 5 (SKU-004): Shelf = 20 (≥ 5), Warehouse = 25 (≥ 10). Both locations are well-stocked → "OK".
Row 6 (SKU-005): Shelf = 0 (< 5), Warehouse = 5 (< 10). Critical shortage → "REORDER".

2. Summary Auditing Formulas (B9 & B10)

Cell B9 (Total SKUs): =COUNTA(A2:A6) counts non-empty SKU codes, returning 5.
Cell B10 (Needs Reorder): =COUNTIF(D2:D6, "REORDER") tallies rows marked for restock, returning 4.

The OR function is ideal when an alert should trigger if at least one warning condition is met. If an item had to fail both criteria simultaneously, you would use AND instead.