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".