FILTER function in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a list of all sales transactions where the sales amount is greater than $500.

📊 Data: You have a sales data table with columns: Transaction ID, Date, Salesperson, Region, and Sales Amount.

🎯 Deliverable: Create a filtered list showing only transactions with sales amounts greater than $500.

Sample Data

Transaction ID	Date	Salesperson	Region	Sales Amount
T001	2024-05-01	Alice	North	450
T002	2024-05-02	Bob	East	700
T003	2024-05-03	Charlie	South	300
T004	2024-05-04	Diana	West	850
T005	2024-05-05	Eva	North	600
T006	2024-05-06	Frank	East	200
T007	2024-05-07	Grace	South	950
T008	2024-05-08	Hank	West	400

1

Step 1: Select a blank cell where you want the filtered list to start, for example, cell G2.

Expected Result

The cell is ready to enter the formula.

2

Step 2: Enter the FILTER formula to show all rows where Sales Amount is greater than 500.

=FILTER(A2:E9, E2:E9 > 500)

Expected Result

The filtered list shows transactions T002, T004, T005, and T007 with all their details.

3

Step 3: Add headers above the filtered data to label the columns.

Manually type headers 'Transaction ID', 'Date', 'Salesperson', 'Region', 'Sales Amount' in cells G1 to K1.

Expected Result

Headers appear above the filtered data for clarity.

Final Result

Transaction ID | Date       | Salesperson | Region | Sales Amount
-------------- | ---------- | ----------- | ------ | ------------
T002           | 2024-05-02 | Bob         | East   | 700
T004           | 2024-05-04 | Diana       | West   | 850
T005           | 2024-05-05 | Eva         | North  | 600
T007           | 2024-05-07 | Grace       | South  | 950

✓Only transactions with sales amounts greater than $500 are shown.

✓This helps the manager focus on high-value sales quickly.

✓The FILTER function dynamically updates if the data changes.

Bonus Challenge

Modify the FILTER formula to show only transactions with sales amounts greater than $500 AND from the 'East' or 'West' regions.

Show Hint

Use the FILTER function with multiple conditions combined using the multiplication (*) sign for AND logic and plus (+) sign for OR logic, like: =FILTER(A2:E9, (E2:E9 > 500) * ((D2:D9 = "East") + (D2:D9 = "West")))

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