AND and OR functions in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to identify which sales transactions meet certain criteria for special promotions.

📊 Data: You have a table with sales data including columns for Salesperson, Region, Sales Amount, and Product Category.

🎯 Deliverable: Create columns that use AND and OR functions to flag transactions that qualify for two different promotion rules.

Sample Data

Salesperson	Region	Sales Amount	Product Category
Alice	East	1200	Electronics
Bob	West	800	Furniture
Charlie	East	1500	Electronics
Diana	South	700	Clothing
Eva	West	1300	Electronics
Frank	East	600	Furniture
Grace	South	1100	Electronics
Hank	West	900	Clothing

1

Step 1: Add a new column named 'Promo Rule 1' to check if the sale is in the East region AND the sales amount is greater than 1000.

=AND(B2="East", C2>1000)

Expected Result

For row 2 (Alice), result is TRUE because Region is East and Sales Amount is 1200.

2

Step 2: Copy the formula from step 1 down the 'Promo Rule 1' column for all rows.

Drag the formula down from row 2 to row 9.

Expected Result

Rows with TRUE: Alice (row 2), Charlie (row 4). Others FALSE.

3

Step 3: Add another new column named 'Promo Rule 2' to check if the sale is either in the West region OR the product category is Electronics.

=OR(B2="West", D2="Electronics")

Expected Result

For row 2 (Alice), result is TRUE because Product Category is Electronics.

4

Step 4: Copy the formula from step 3 down the 'Promo Rule 2' column for all rows.

Drag the formula down from row 2 to row 9.

Expected Result

Rows with TRUE: Alice, Bob, Charlie, Eva, Grace, Hank. Rows with FALSE: Diana, Frank.

5

Step 5: Filter the data to show only rows where 'Promo Rule 1' is TRUE to see sales qualifying for the first promotion.

Use filter on 'Promo Rule 1' column for TRUE values.

Expected Result

Filtered rows: Alice, Charlie.

6

Step 6: Filter the data to show only rows where 'Promo Rule 2' is TRUE to see sales qualifying for the second promotion.

Use filter on 'Promo Rule 2' column for TRUE values.

Expected Result

Filtered rows: Alice, Bob, Charlie, Eva, Grace, Hank.

Final Result

Salesperson | Region | Sales Amount | Product Category | Promo Rule 1 | Promo Rule 2
-------------------------------------------------------------------------------------
Alice       | East   | 1200         | Electronics      | TRUE         | TRUE
Bob         | West   | 800          | Furniture        | FALSE        | TRUE
Charlie     | East   | 1500         | Electronics      | TRUE         | TRUE
Diana       | South  | 700          | Clothing         | FALSE        | FALSE
Eva         | West   | 1300         | Electronics      | FALSE        | TRUE
Frank       | East   | 600          | Furniture        | FALSE        | FALSE
Grace       | South  | 1100         | Electronics      | FALSE        | TRUE
Hank        | West   | 900          | Clothing         | FALSE        | TRUE

Bonus Challenge

Create a third column that flags sales qualifying for both promotions at the same time using AND and OR functions.

Show Hint

Use =AND(AND(B2="East", C2>1000), OR(B2="West", D2="Electronics")) or combine the previous columns with AND.