Array formulas basics in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to calculate the total sales for each product category and also find the average sales per product using array formulas.

📊 Data: You have a table with columns: Product, Category, and Sales. Each row shows a product's sales amount and its category.

🎯 Deliverable: Create array formulas to calculate total sales by category and average sales per product, and present the results clearly.

Sample Data

Product	Category	Sales
Shirt	Clothing	150
Jeans	Clothing	200
Hat	Accessories	50
Belt	Accessories	70
Shoes	Footwear	300
Socks	Footwear	80
Jacket	Clothing	250
Scarf	Accessories	60

1

Step 1: List unique product categories in a new column starting from cell E2.

Manually type Clothing, Accessories, Footwear in cells E2, E3, and E4.

Expected Result

E2=Clothing, E3=Accessories, E4=Footwear

2

Step 2: Calculate total sales for each category using an array formula in cell F2 and copy down to F4.

=SUM(IF($B$2:$B$9=E2,$C$2:$C$9,0))

Expected Result

F2=600, F3=180, F4=380

3

Step 3: Calculate average sales per product using an array formula in cell G2.

=AVERAGE($C$2:$C$9)

Expected Result

G2=145

4

Step 4: Press Ctrl+Shift+Enter to enter the array formula in step 2 so Excel treats it as an array formula.

No formula, just confirm array entry.

Expected Result

Formula in F2 shows curly braces { } indicating array formula.

Final Result

Category    Total Sales
Clothing   600
Accessories 180
Footwear   380

Average Sales per Product: 145

✓Clothing category has the highest total sales of 600.

✓Accessories have the lowest total sales of 180.

✓Average sales per product across all categories is 145.

Bonus Challenge

Create a single array formula that returns total sales for all categories in one step without listing categories manually.

Show Hint

Use UNIQUE function to get categories and SUMIF inside an array formula.