Why array formulas process ranges at once in Google Sheets - Business Case Study

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 quickly and efficiently using Google Sheets.

📊 Data: You have a table with product names, categories, and sales amounts for each transaction.

🎯 Deliverable: Create a summary table that shows each product category and the total sales using an array formula that processes all data at once.

Sample Data

Product	Category	Sales
Shirt	Clothing	50
Pants	Clothing	70
Hat	Accessories	30
Socks	Clothing	20
Belt	Accessories	40
Sunglasses	Accessories	60
Shoes	Footwear	100
Sandals	Footwear	80

1

Step 1: List unique product categories in a column to prepare for summary.

=UNIQUE(B2:B9)

Expected Result

Clothing, Accessories, Footwear (each in separate cells)

2

Step 2: Use an array formula to calculate total sales for each category at once.

=ARRAYFORMULA(SUMIF(B2:B9, E2:E4, C2:C9))

Expected Result

140, 130, 180 (total sales for Clothing, Accessories, Footwear respectively)

3

Step 3: Combine unique categories and their total sales side by side for a clear summary.

Place step 1 formula in E2 and step 2 formula in F2 to fill down automatically.

Expected Result

Column E shows categories; Column F shows total sales matching each category.

Final Result

Category    | Total Sales
-------------------------
Clothing    | 140
Accessories | 130
Footwear    | 180

✓Clothing category has total sales of 140.

✓Accessories category has total sales of 130.

✓Footwear category leads with total sales of 180.

Bonus Challenge

Create a dynamic summary that updates automatically when new sales data is added.

Show Hint

Use open-ended ranges like B2:B and C2:C in your formulas and place the summary table below the data.