FILTER function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of FILTER with multiple conditions

Given the data in cells A1:A5 as {10, 20, 30, 40, 50} and B1:B5 as {5, 15, 25, 35, 45}, what is the output of the formula =FILTER(A1:A5, A1:A5 > 20, B1:B5 < 40)?

A {30, 40}

B {30, 40, 50}

C {40}

D {50}

💡 Hint

Remember FILTER returns values where all conditions are TRUE for each row.

The formula filters values in A1:A5 where A is greater than 20 and B is less than 40. Values 30 and 40 in A meet A>20, and their corresponding B values 25 and 35 are less than 40. 50 is excluded because B=45 is not less than 40.

❓ Function Choice

intermediate

Choosing the correct FILTER formula for text matching

You have a list of names in A1:A6: {"Anna", "Bob", "Annie", "Bill", "Ann", "Ben"}. Which FILTER formula returns only names starting with "Ann"?

A =FILTER(A1:A6, A1:A6 = "Ann*")

B =FILTER(A1:A6, REGEXMATCH(A1:A6, "^Ann"))

C =FILTER(A1:A6, SEARCH("Ann", A1:A6))

D =FILTER(A1:A6, LEFT(A1:A6, 3) = "Ann")

💡 Hint

Use a function that can match text patterns at the start of strings.

Option B uses REGEXMATCH with ^Ann to match names starting exactly with 'Ann'. Option B works but is less flexible and can cause errors if cells are empty. Option B is invalid syntax. Option B returns numbers or errors, not booleans.

🎯 Scenario

advanced

Filter rows with non-empty and numeric values

You have data in A1:A6: {"12", "", "abc", "34", " ", "56"}. Which FILTER formula returns only the numeric values ignoring blanks and text?

A =FILTER(A1:A6, ISNUMBER(VALUE(A1:A6)))

B =FILTER(A1:A6, ISNUMBER(A1:A6))

C =FILTER(A1:A6, NOT(ISBLANK(A1:A6)))

D =FILTER(A1:A6, ISNUMBER(N(A1:A6)))

💡 Hint

Convert text to numbers and check if conversion is valid.

Option A converts text to numbers with VALUE and checks if result is a number. It filters out blanks and non-numeric text. Option A fails because A1:A6 are text, so ISNUMBER returns FALSE. Option A includes text and blanks with spaces. Option A returns 0 for text, so ISNUMBER(N()) is TRUE for blanks and text, which is incorrect.

❓ data_analysis

advanced

Count filtered results with multiple criteria

Given sales data in columns A (Product) and B (Quantity): A1:A5 = {"Pen", "Pencil", "Pen", "Eraser", "Pen"}, B1:B5 = {10, 5, 15, 7, 20}. Which formula counts how many rows have Product = "Pen" and Quantity > 10?

A =COUNTIF(A1:A5, "Pen") + COUNTIF(B1:B5, ">10")

B =COUNT(FILTER(A1:A5, A1:A5 = "Pen", B1:B5 > 10))

C =SUMPRODUCT((A1:A5 = "Pen") * (B1:B5 > 10))

D =COUNTA(FILTER(A1:A5, A1:A5 = "Pen", B1:B5 > 10))

💡 Hint

FILTER returns matching rows; use COUNTA to count them.

Option D filters rows where Product is 'Pen' and Quantity > 10, then counts them with COUNTA. Option D returns 0 because COUNT counts numbers but A1:A5 are text. Option D sums counts separately, not combined. Option D is valid but uses SUMPRODUCT, not FILTER.

📊 Formula Result

expert

FILTER with dynamic array and error handling

Given A1:A4 = {5, 10, 15, 20}, what is the output of =IFERROR(FILTER(A1:A4, A1:A4 > 25), "No results")?

A Error #N/A

B {} (empty array)

C "No results"

D 5

💡 Hint

FILTER returns an error if no rows match; IFERROR catches it.

No values in A1:A4 are greater than 25, so FILTER returns an error. IFERROR replaces this error with the text "No results".

Practice

1. What does the FILTER function do in Google Sheets?

easy

A. It changes the color of cells.

B. It sorts data alphabetically.

C. It returns only the rows that meet certain conditions.

D. It adds numbers in a range.

Solution

1. Step 1: Recall the purpose of the FILTER function

   The FILTER function is designed to pick and show only the rows that match given rules or conditions. Sorting, adding, or coloring cells are different functions and not what FILTER does.

2. Final Answer:

   It returns only the rows that meet certain conditions. -> Option C

3. Quick Check:

   FILTER picks matching rows = It returns only the rows that meet certain conditions. [OK]

Hint: FILTER shows only rows matching your rules [OK]

Common Mistakes:

• Thinking FILTER sorts data
• Confusing FILTER with SUM or SORT
• Assuming FILTER changes cell colors

2. Which of these is the correct syntax for the FILTER function?

easy

A. =FILTER(range, condition1 AND condition2)

B. =FILTER(condition1, condition2, range)

C. =FILTER(range; condition1; condition2)

D. =FILTER(range, condition1, condition2, ...)

Solution

1. Step 1: Verify the correct FILTER syntax

   =FILTER(range, condition1, condition2, ...) has range first, then conditions (A). B wrong order, C semicolons invalid, D AND inside incorrect.

2. Final Answer:

   =FILTER(range, condition1, condition2, ...) -> Option D

3. Quick Check:

   FILTER syntax = range first, then conditions [OK]

Hint: FILTER always starts with range, then conditions [OK]

Common Mistakes:

• Putting conditions before range
• Using semicolons instead of commas
• Trying to combine conditions with AND inside FILTER

3. Given this data in A1:B5:

A1: Name  B1: Score
A2: Amy   B2: 85
A3: Bob   B3: 90
A4: Cara  B4: 75
A5: Dan   B5: 95

What will =FILTER(A2:A5, B2:B5 > 80) return?

medium

A. ["Amy", "Cara", "Dan"]

B. ["Amy", "Bob", "Dan"]

C. ["Bob", "Dan"]

D. ["Cara"]

Solution

1. Step 1: Evaluate the FILTER formula on the data

   Filters A2:A5 where B2:B5 > 80: Amy(85) yes, Bob(90) yes, Cara(75) no, Dan(95) yes. Returns ["Amy", "Bob", "Dan"].

2. Final Answer:

   ["Amy", "Bob", "Dan"] -> Option B

3. Quick Check:

   FILTER names with scores > 80 = Amy, Bob, Dan [OK]

Hint: Match condition to filter rows, then list matching names [OK]

Common Mistakes:

• Including Cara who scored 75
• Only listing Bob and Dan, missing Amy
• Confusing ranges and conditions

4. This formula gives an error: =FILTER(A2:A5, B2:B5 > 80, C2:C5 = "Yes")
Given columns A and B have data but C is empty. What is the fix?

medium

A. Remove the condition on C2:C5 from the formula.

B. Fill column C with values before using FILTER.

C. Change the formula to =FILTER(A2:A5, B2:B5 > 80) and add IFERROR.

D. Use =FILTER(A2:A5, B2:B5 > 80, ISBLANK(C2:C5)) instead.

Solution

1. Step 1: Identify and fix the FILTER error

   Column C empty makes C2:C5="Yes" always FALSE, no matches, #N/A error. Fix: remove C2:C5 condition.

2. Final Answer:

   Remove the condition on C2:C5 from the formula. -> Option A

3. Quick Check:

   Remove empty column condition to fix FILTER error [OK]

Hint: Remove conditions on empty columns to avoid errors [OK]

Common Mistakes:

• Trying to fix with IFERROR without fixing condition
• Filling column C unnecessarily
• Using ISBLANK incorrectly in FILTER

5. You have a sales table with columns: Product (A2:A10), Region (B2:B10), and Sales (C2:C10).

You want to list all products sold in the "East" region with sales over 1000. Which formula correctly does this?

hard

A. =FILTER(A2:A10, B2:B10 = "East", C2:C10 > 1000)

B. =FILTER(A2:A10, B2:B10 = "East" AND C2:C10 > 1000)

C. =FILTER(A2:A10, (B2:B10 = "East") + (C2:C10 > 1000))

D. =FILTER(A2:A10, OR(B2:B10 = "East", C2:C10 > 1000))

Solution

1. Step 1: Identify the correct FILTER formula for AND conditions

   Commas separate conditions (AND logic): =FILTER(A2:A10, B2:B10 = "East", C2:C10 > 1000) (B). A AND invalid, C + incorrect, D OR wrong.

2. Final Answer:

   =FILTER(A2:A10, B2:B10 = "East", C2:C10 > 1000) -> Option A

3. Quick Check:

   Multiple conditions in FILTER use commas = =FILTER(A2:A10, B2:B10 = "East", C2:C10 > 1000) [OK]

Hint: Use commas for AND conditions in FILTER [OK]

Common Mistakes:

• Using AND or OR inside FILTER incorrectly
• Using + to combine conditions
• Confusing AND with OR logic