Count the orders that are big AND fast (SUMPRODUCT)
The problem
Problem statement
Sourcing wants the orders that cleared 100 units and still shipped inside 5 days. Units are in B2:B6, days to ship in C2:C6, and the thresholds are written on the sheet (E1, E2). Put the count in the Both cell F3.
One formula must handle both conditions at once — no helper column, no two separate counts added together.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | |
|---|---|---|---|---|
| 1 | Order | Units | Days to ship | Units > 100 |
| 2 | SO-1 | 120 | 4 | Days < 5 |
| 3 | SO-2 | 80 | 9 | Both |
| 4 | SO-3 | 300 | 3 | |
| 5 | SO-4 | 60 | 7 | |
| 6 | SO-5 | 210 | 2 |
Notes
SUMPRODUCT((B2:B6>100)*(C2:C6<5)) multiplies two arrays of TRUE/FALSE. Multiplication behaves as AND (1*1 = 1, anything with 0 is 0), and SUMPRODUCT adds the products, so the total is the number of rows where both hold. COUNTIFS does the same job with criteria strings when the comparisons are simple.
How to approach
Your answer goes in F3. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is F3
- Build two TRUE/FALSE arrays: B2:B6>100 and C2:C6<5
- Multiply them so only rows that are both survive
- Shape: =SUMPRODUCT((?)*(?) )
Official solutions
Show 2 solutions — try it yourself first
Approach 1: multiply two boolean arrays
=SUMPRODUCT((B2:B6>100)*(C2:C6<5))
SO-1 (120, 4), SO-3 (300, 3) and SO-5 (210, 2) satisfy both; 1+0+1+0+1 = 3. The multiplication is the AND, the SUMPRODUCT is the count.
Approach 2: COUNTIFS with two numeric criteria
=COUNTIFS(B2:B6, ">100", C2:C6, "<5")
Same answer with plain criteria strings. COUNTIFS is easier to read for simple thresholds; SUMPRODUCT is the one that still works when a condition needs a function inside it (LEFT, MONTH, ISNUMBER).
Function guide
FAQ
How do I count rows that meet two numeric conditions?
=SUMPRODUCT((range1>a)*(range2<b)) or =COUNTIFS(range1, ">"&a, range2, "<"&b). Both count rows where every condition holds.
Why does SUMPRODUCT need the parentheses?
Without them the comparisons bind differently: SUMPRODUCT(B2:B6>100) would evaluate only the first element. Wrapping each comparison creates the full array before multiplying.