Combining clauses in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a report showing total sales for each region but only for sales above $500 and made by salespeople with more than 3 years of experience.

📊 Data: You have a sales data table with columns: Salesperson, Region, Sales Amount, and Years of Experience.

🎯 Deliverable: Create a summary table that shows each region and the total sales amount meeting the criteria.

Sample Data

Salesperson	Region	Sales Amount	Years of Experience
Alice	East	600	4
Bob	West	450	5
Charlie	East	700	2
Diana	North	800	6
Eva	South	300	7
Frank	West	900	4
Grace	North	400	3
Hank	South	750	5

1

Step 1: Create a list of unique regions from the data.

=UNIQUE(B2:B9)

Expected Result

East, West, North, South

2

Step 2: Calculate total sales per region where sales amount is greater than 500 AND years of experience is more than 3.

=SUMIFS(C2:C9, B2:B9, E2, C2:C9, ">500", D2:D9, ">3")

Expected Result

For East: 600; West: 900; North: 800; South: 750

3

Step 3: Copy the formula down for all unique regions to get totals for each.

Drag the formula from step 2 down alongside the unique regions list.

Expected Result

Totals per region as above aligned with each region.

Final Result

Region | Total Sales
-------------------
East   | 600
West   | 900
North  | 800
South  | 750

✓West region has the highest total sales meeting the criteria with $900.

✓Salespeople with more than 3 years experience and sales above $500 contribute significantly to sales.

✓East region's total sales are lower because one salesperson with low experience had a high sale that was excluded.

Bonus Challenge

Add a filter to show only regions with total sales above $700.

Show Hint

Use FILTER function with condition on the total sales column.