Advanced filtering criteria in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Filter rows where sales are above 1000 and region is "West"

Given a table with columns Sales in column A and Region in column B, which formula correctly filters rows where sales are greater than 1000 and region is "West"?

A =FILTER(A2:B10, (A2:A10>1000)+(B2:B10="West"))

B =FILTER(A2:B10, (A2:A10>1000)*(B2:B10="West"))

C =FILTER(A2:B10, (A2:A10>1000)&(B2:B10="West"))

D =FILTER(A2:B10, AND(A2:A10>1000, B2:B10="West"))

💡 Hint

Use multiplication (*) to combine multiple conditions in FILTER for AND logic.

The FILTER function in Excel uses multiplication (*) to represent AND logic between conditions. Option B correctly filters rows where sales are greater than 1000 and the region is "West".

Option B uses addition (+) which acts like OR, so it includes rows where either condition is true.

Option B uses & which is a text concatenation operator, causing a formula error.

Option B uses AND function which does not work with arrays inside FILTER.

❓ Function Choice

intermediate

Choose the function to filter dates in January 2024

You want to filter rows where the date in column C is in January 2024. Which function and criteria combination will correctly filter these rows?

A =FILTER(A2:D100, (MONTH(C2:C100)=1)*(YEAR(C2:C100)=2024))

B =FILTER(A2:D100, (TEXT(C2:C100,"mm/yyyy")="01/2024"))

C =FILTER(A2:D100, (C2:C100>=DATEVALUE("01/01/2024"))*(C2:C100<=DATEVALUE("01/31/2024")))

D =FILTER(A2:D100, (C2:C100>=DATE(2024,1,1))*(C2:C100<=DATE(2024,1,31)))

💡 Hint

Use DATE function to compare dates numerically for filtering.

Option D correctly filters dates between January 1 and January 31, 2024 using numeric date comparisons.

Option D uses MONTH and YEAR functions which work but are less efficient and can cause issues with array handling in some Excel versions.

Option D uses TEXT which converts dates to text and can cause errors or mismatches.

Option D compares dates as text strings which may fail due to date format differences.

❓ data_analysis

advanced

Count filtered rows with multiple conditions

You have a dataset with columns Category in column A and Amount in column B. You want to count how many rows have Category "Food" and Amount greater than 50. Which formula gives the correct count?

A =SUMPRODUCT((A2:A100="Food")*(B2:B100>50))

B =SUM(IF((A2:A100="Food")*(B2:B100>50),1,0))

C =COUNTIFS(A2:A100,"Food", B2:B100,">50")

D =COUNTIF(A2:A100,"Food") + COUNTIF(B2:B100,">50")

💡 Hint

COUNTIFS counts rows matching multiple criteria directly.

Option C uses COUNTIFS which counts rows where both conditions are true, giving the correct count.

Option C uses SUMPRODUCT which also works but is more complex.

Option C is an array formula that requires Ctrl+Shift+Enter in older Excel versions and is less straightforward.

Option C sums counts separately, not considering rows where both conditions are true simultaneously, so it overcounts.

🎯 Scenario

advanced

Filter rows excluding blanks and errors in a column

You want to filter rows from A2:C100 where column B has values that are not blank and do not contain errors. Which formula correctly filters these rows?

A =FILTER(A2:C100, (B2:B100<>"")*(NOT(ISERROR(B2:B100))))

B =FILTER(A2:C100, (B2:B100<>"")*(ISNUMBER(B2:B100)))

C =FILTER(A2:C100, (B2:B100<>"")*(ISERROR(B2:B100)=FALSE))

D =FILTER(A2:C100, (B2:B100<>"")*(ISNUMBER(B2:B100)=FALSE))

💡 Hint

Use ISERROR combined with NOT to exclude error values.

Option A correctly filters rows where column B is not blank and does not contain errors by using NOT(ISERROR()).

Option A only includes numbers, excluding valid non-numeric text.

Option A is logically equivalent to B but ISERROR returns TRUE for errors, so ISERROR=FALSE is correct but less clear than NOT(ISERROR()).

Option A excludes numbers, which is opposite of the goal.

🧠 Conceptual

expert

Understanding FILTER with dynamic array spill and criteria

You apply the formula =FILTER(A2:B10, (A2:A10>5)+(B2:B10<3)). What will be the result?

A Rows where column A is greater than 5 OR column B is less than 3

B Rows where column A is greater than 5 AND column B is less than 3

C Rows where column A is greater than 5 XOR column B is less than 3

D Formula returns a #VALUE! error due to invalid criteria

💡 Hint

Addition (+) between logical arrays acts like OR in FILTER criteria.

The FILTER function treats addition (+) between logical arrays as OR logic, so it returns rows where either condition is true.

Option A describes AND logic which requires multiplication (*).

Option A describes exclusive OR which FILTER does not support directly.

Option A is incorrect because the formula is valid and returns results.