Dynamic arrays › FILTER103 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeFILTER

FILTER with two conditions: low shirt stock

Fajar runs the warehouse of a school uniform shop in Solo. Ahead of the new school year, he needs a list of items in a given category whose stock has dropped below a limit, so he can reorder them from the garment maker right away.

Task

In F5, write one FILTER formula that returns all columns of A2:D11 for items whose category equals G1 and whose stock is less than G2. Refer to cells G1 and G2 instead of retyping their values. The result spills into F5:I7.

Cells to fillF5:I7

Required functionsFILTER

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
Two conditions that must both hold are combined by multiplying them: (condition1)*(condition2). TRUE × TRUE = 1, anything else gives 0.
Hint 2
Each condition compares a whole column and sits inside parentheses, for example (C2:C11=G1).
Hint 3
Pattern: =FILTER(A2:D11, (C2:C11=G1)*(…)). Complete the stock condition in the second pair of parentheses.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

F5

=FILTER(A2:D11,(C2:C11=G1)*(D2:D11<G2))

Explanation

=FILTER(A2:D11,(C2:C11=G1)*(D2:D11<G2)) keeps only the rows that pass both conditions: SRG-01, SRG-05 and SRG-09. The senior high shirt (stock 22) and the primary trousers (stock 8) are left out because each meets only one condition. Do not use AND(...) inside FILTER: AND collapses the whole column into a single TRUE/FALSE. For "or" conditions, add instead of multiply: (condition1)+(condition2).

Starting worksheet: Sheet1
Row	A	B	C	D	F	G	H	I
1	Code	Item	Category	Stock	Category	Shirt
2	SRG-01	White shirt, primary	Shirt	12	Stock below	20
3	SRG-02	Red trousers, primary	Trousers	8
4	SRG-03	White shirt, junior high	Shirt	35	Code	Item	Category	Stock
5	SRG-04	Blue skirt, junior high	Skirt	15
6	SRG-05	Scout shirt	Shirt	18
7	SRG-06	Grey trousers, senior high	Trousers	40
8	SRG-07	White shirt, senior high	Shirt	22
9	SRG-08	Grey tie, senior high	Tie	9
10	SRG-09	School batik shirt	Shirt	6
11	SRG-10	Grey skirt, senior high	Skirt	27
12
13
14

Fill in the requested cell, then press Check answer.

Validation result