Combining clauses in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This table lists 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

Formula Trace

=FILTER(A2:A5, (B2:B5>27) * (C2:C5="New York"))

Step 1: B2:B5>27

Step 2: C2:C5="New York"

Step 3: (B2:B5>27) * (C2:C5="New York")

Step 4: FILTER(A2:A5, [1, 0, 1, 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  |

Formula references:
- A2:A5 for names
- B2:B5 for ages
- C2:C5 for cities

The formula uses the names in A2:A5, ages in B2:B5, and cities in C2:C5 to filter names based on age and city conditions.

Result

Filtered Names
1 | Result
2 | Alice
3 | Charlie

The formula returns the names of people older than 27 who live in New York: Alice and Charlie.

Sheet Trace Quiz - 3 Questions

What does the expression (B2:B5>27) evaluate to?

A [FALSE, TRUE, FALSE, FALSE]

B [TRUE, FALSE, TRUE, TRUE]

C [TRUE, TRUE, TRUE, TRUE]

D [FALSE, FALSE, FALSE, FALSE]

Key Result

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