COUNTIF and COUNTIFS in Excel - Real Business Scenario

Scenario Mode

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

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

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

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

Sample Data

Transaction ID	Region	Salesperson	Product	Sales Amount
1001	East	Alice	Widget	250
1002	West	Bob	Gadget	150
1003	East	Charlie	Widget	300
1004	North	Alice	Gizmo	200
1005	South	Bob	Widget	180
1006	East	Charlie	Gadget	220
1007	West	Alice	Gizmo	170
1008	South	Charlie	Widget	210
1009	North	Bob	Gadget	190
1010	East	Alice	Gizmo	230

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 had a Sales Amount greater than 200 using COUNTIF.

=COUNTIF(E2:E11, ">200")

Expected Result

5

3

Step 3: Count how many sales transactions happened in the East region AND had a Sales Amount greater than 200 using COUNTIFS.

=COUNTIFS(B2:B11, "East", E2:E11, ">200")

Expected Result

4

4

Step 4: Count how many sales transactions were made by Alice in the West region using COUNTIFS.

=COUNTIFS(C2:C11, "Alice", B2:B11, "West")

Expected Result

1

5

Step 5: Count how many sales transactions were for the product 'Widget' with Sales Amount less than or equal to 210 using COUNTIFS.

=COUNTIFS(D2:D11, "Widget", E2:E11, "<=210")

Expected Result

2

Final Result

Sales Count Summary
-------------------
East Region Sales: 4
Sales > 200: 5
East Sales > 200: 4
Alice in West Region: 1
Widget Sales <= 210: 2

✓There are 4 sales transactions in the East region.

✓Five sales transactions have amounts greater than 200.

✓Four sales in the East region had sales amounts above 200.

✓Alice made 1 sale in the West region.

✓Two Widget sales had amounts less than or equal to 210.

Bonus Challenge

Create a formula to count how many sales transactions were made by Bob or Charlie in the South region with sales amounts between 150 and 220 inclusive.

Show Hint

Use COUNTIFS with multiple criteria and combine conditions with addition or use COUNTIFS twice and add results.