Advanced filtering criteria in Excel - Cell-by-Cell Formula Trace

Sample Data

This table shows people with their names, ages, and cities.

Cell	Value
A1	Name
B1	Age
C1	City
A2	Alice
B2	30
C2	New York
A3	Bob
B3	25
C3	Los Angeles
A4	Charlie
B4	35
C4	New York
A5	Diana
B5	28
C5	Chicago
A6	Ethan
B6	40
C6	New York

Formula Trace

=FILTER(A2:C6, (B2:B6>28)*(C2:C6="New York"))

Step 1: B2:B6>28

Step 2: C2:C6="New York"

Step 3: (B2:B6>28)*(C2:C6="New York")

Step 4: FILTER(A2:C6, [1,0,1,0,0])

Cell Reference Map

    A       B          C
1 |Name | Age | City     |
2 |Alice| 30  | New York |
3 |Bob  | 25  | Los Angeles|
4 |Charlie|35 | New York |
5 |Diana| 28  | Chicago  |
6 |Ethan| 40  | New York |

References:
- B2:B6 for Age
- C2:C6 for City
- A2:C6 for full data to filter

The formula uses ages in B2:B6 and cities in C2:C6 to filter the full data range A2:C6.

Result

    A       B     C
1 |Name | Age | City     |
2 |Alice| 30  | New York |
3 |Charlie|35 | New York |

The filtered result shows only people older than 28 who live in New York.

Sheet Trace Quiz - 3 Questions

What does the expression B2:B6>28 return?

A [FALSE, TRUE, FALSE, TRUE, FALSE]

B [TRUE, FALSE, TRUE, FALSE, TRUE]

C [30, 25, 35, 28, 40]

D [1, 0, 1, 0, 1]

Key Result

FILTER(data_range, (condition1)*(condition2)) filters rows where both conditions are true.