COUNTIF and COUNTIFS in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to know how many sales transactions meet certain conditions, such as sales in a specific region or sales above a certain amount.

📊 Data: You have a table with sales data including columns for Transaction ID, Region, Salesperson, and Sale Amount.

🎯 Deliverable: Create formulas using COUNTIF and COUNTIFS to count transactions by region and by multiple conditions.

Sample Data

Transaction ID	Region	Salesperson	Sale Amount
1001	East	Alice	250
1002	West	Bob	450
1003	East	Charlie	300
1004	North	Alice	150
1005	South	David	500
1006	West	Eva	200
1007	East	Bob	350
1008	South	Charlie	400
1009	North	David	100
1010	East	Eva	450

1

Step 1: Count how many sales transactions happened in the East region using COUNTIF.

=COUNTIF(B2:B11, "East")

Expected Result

4

2

Step 2: Count how many sales transactions have a Sale Amount greater than 300 using COUNTIF.

=COUNTIF(D2:D11, ">300")

Expected Result

5

3

Step 3: Count how many sales transactions happened in the East region with Sale Amount greater than 300 using COUNTIFS.

=COUNTIFS(B2:B11, "East", D2:D11, ">300")

Expected Result

3

4

Step 4: Count how many sales transactions were made by salesperson Bob with Sale Amount less than or equal to 350 using COUNTIFS.

=COUNTIFS(C2:C11, "Bob", D2:D11, "<=350")

Expected Result

1

Final Result

Count of sales in East region: 4
Count of sales with amount > 300: 5
Count of sales in East region with amount > 300: 3
Count of sales by Bob with amount <= 350: 1

✓East region has 4 sales transactions.

✓There are 5 sales transactions with amounts greater than 300.

✓3 sales in East region have amounts greater than 300.

✓Bob made 1 sale with amount less than or equal to 350.

Bonus Challenge

Create a formula to count how many sales transactions happened in the South or West regions with Sale Amount between 200 and 500 inclusive.

Show Hint

Use COUNTIFS with multiple criteria and combine counts for South and West regions using addition.