COUNTIF with the limit in a cell
PremiumIn E2, count the products in B2:B7 with stock below the reorder level in E1. Join "<" and E1 with &, so the count follows when E1 changes.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Stock | Reorder level | 40 | ||
| 2 | Pens | 35 | To reorder | |||
| 3 | Paper | 120 | ||||
| 4 | Ink | 12 | ||||
| 5 | Folders | 48 | ||||
| 6 | Staplers | 9 | ||||
| 7 | Tape | 60 | ||||
| 8 |
Premium lesson
Unlock COUNTIF and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test (SHL format)
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
Why this works
The & operator joins the text "<" and the value of E1 into the criterion "<40". Change E1 to 50 and the count updates by itself.
Common mistake: Putting the cell inside the quotes, like "<E1". Excel then looks for the text E1; join it with & instead: "<"&E1.
The answer
- E2
=COUNTIF(1B2:B7,2"<"&E1)
In plain words: Count the stock levels 1in B2:B7 that are 2below the reorder level in E1.