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.
Next with premium:SUMIFAVERAGEIF and AVERAGEIFS
It comes up in this mock test: Excel test (SHL format)
Need help?
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.