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.

Practice

1.

What does having multiple rows in an advanced filter criteria range represent?

easy

A. Each row represents an OR condition between the rows

B. Each row represents an AND condition between the rows

C. Each row is ignored except the first one

D. Each row duplicates the filter results

Solution

1. Step 1: Understand criteria range rows

   In advanced filtering, each row in the criteria range is treated as a separate condition set.

2. Step 2: Identify logical relationship between rows

   Multiple rows mean the filter will include rows matching any one of the criteria rows, which is an OR logic.

3. Final Answer:

   Each row represents an OR condition between the rows -> Option A

4. Quick Check:

   Multiple rows = OR logic [OK]

Hint: Multiple criteria rows mean OR conditions between them [OK]

Common Mistakes:

• Thinking multiple rows mean AND conditions
• Assuming only the first row is used
• Believing rows duplicate results

2.

Which of the following is the correct way to specify a criteria for filtering values greater than 100 in a column named Sales?

easy

A. =Sales>100 under the Sales header

B. >100 under the Sales header in the criteria range

C. Sales>100 in any cell of the criteria range

D. Sales>100 under the Sales header

Solution

1. Step 1: Understand criteria syntax for filtering

   In advanced filter criteria, you write the condition directly under the column header, e.g., >100 for values greater than 100.

2. Step 2: Identify correct syntax for 'greater than'

   The correct way is to put >100 under the Sales header, not including the column name or equals sign.

3. Final Answer:

   >100 under the Sales header in the criteria range -> Option B

4. Quick Check:

   Use operator and value only under header [OK]

Hint: Put only operator and value under header, no column name [OK]

Common Mistakes:

• Including column name in criteria cell
• Using equals sign (=) before condition
• Writing condition outside the criteria range

3.

Given this criteria range for filtering a table with columns Region and Sales:

Region | Sales
East   | >500
West   | <=300

Which rows will be included in the filtered results?

medium

A. Rows where Region is East OR Sales > 500 AND Region is West OR Sales <= 300

B. Rows where Region is East OR Sales > 500 OR Region is West OR Sales <= 300

C. Rows where Region is East AND Sales > 500 OR Region is West AND Sales <= 300

D. Rows where Region is East AND Sales > 500 AND Region is West AND Sales <= 300

Solution

1. Step 1: Analyze each criteria row as AND conditions

   Each row combines conditions with AND, so first row means Region=East AND Sales>500.

2. Step 2: Combine rows with OR logic

   Multiple rows mean OR between them, so second row means Region=West AND Sales<=300.

3. Final Answer:

   Rows where Region is East AND Sales > 500 OR Region is West AND Sales <= 300 -> Option C

4. Quick Check:

   Rows = (East AND >500) OR (West AND <=300) [OK]

Hint: Rows combine AND; multiple rows combine OR [OK]

Common Mistakes:

• Mixing AND and OR incorrectly between rows
• Assuming all conditions apply together
• Ignoring operator precedence in criteria

4.

You set up this criteria range to filter a list with columns Product and Price:

Product | Price
Apple   | >50
Apple   | <=30

But the filter returns no results. What is the likely problem?

medium

A. The criteria rows are combined with OR, so no product can be both >50 and <=30 at once

B. The criteria rows are combined with AND, so no product can be both >50 and <=30 at once

C. The criteria range headers are incorrect

D. The filter only works with numeric columns

Solution

1. Step 1: Understand criteria row logic

   Each row is a separate OR condition, so filter includes rows matching any row.

2. Step 2: Analyze the criteria rows

   Both rows specify Product=Apple but with conflicting Price conditions (>50 OR <=30).

3. Step 3: Why no results?

   If no Apple products have Price >50 or <=30, or data is inconsistent, no rows match.

4. Final Answer:

   The criteria rows are combined with OR, so no product can be both >50 and <=30 at once -> Option A

5. Quick Check:

   Multiple rows = OR, conflicting conditions cause no match [OK]

Hint: Multiple rows mean OR; conflicting conditions may exclude all [OK]

Common Mistakes:

• Thinking rows combine with AND
• Ignoring header names
• Assuming filter only works on numbers

5.

You want to filter a list with columns Category, Quantity, and Status to show rows where:

• Category is "Fruit" and Quantity > 10
• OR Status is "Available" and Quantity <= 5

How should you set up the criteria range?

hard

A. Category | Quantity | Status
Fruit | >10 |
Fruit | <=5 | Available

B. Category | Quantity | Status
Fruit | >10 |
| | Available
| <=5 |

C. Category | Quantity | Status
Fruit | >10 | Available
| <=5 |

D. Category | Quantity | Status
Fruit | >10 |
| <=5 | Available

Solution

1. Step 1: Understand AND and OR in criteria range

   Conditions in the same row combine with AND; multiple rows combine with OR.

2. Step 2: Set first row for Category=Fruit AND Quantity>10

   Put "Fruit" under Category and ">10" under Quantity in first row.

3. Step 3: Set second row for Status=Available AND Quantity<=5

   Put "Available" under Status and "<=5" under Quantity in second row; leave Category blank.

4. Final Answer:

   Category | Quantity | Status Fruit | >10 | | <=5 | Available -> Option D

5. Quick Check:

   Same row = AND; different rows = OR [OK]

Hint: Same row conditions AND; different rows OR [OK]

Common Mistakes:

• Putting all conditions in one row (AND all)
• Misplacing values under wrong headers
• Leaving required cells blank incorrectly