ARRAYFORMULA function in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a quick way to calculate total sales for each product by multiplying quantity sold by unit price for all rows without typing formulas repeatedly.

📊 Data: You have a table with columns: Product, Quantity Sold, and Unit Price for 10 sales records.

🎯 Deliverable: Create a column that uses ARRAYFORMULA to calculate total sales for all products at once.

Sample Data

Product	Quantity Sold	Unit Price
Apples	10	0.5
Bananas	5	0.3
Cherries	20	1.2
Dates	7	1.5
Elderberries	3	2.0
Figs	12	1.1
Grapes	15	0.8
Honeydew	8	3.0
Indian Fig	6	2.5
Jackfruit	4	1.8

1

Step 1: Insert a new column next to Unit Price and label it 'Total Sales'.

Expected Result

A new empty column labeled 'Total Sales' appears next to Unit Price.

2

Step 2: In the first cell under 'Total Sales' (e.g., D2), enter the ARRAYFORMULA to multiply Quantity Sold by Unit Price for all rows.

=ARRAYFORMULA(B2:B11 * C2:C11)

Expected Result

The 'Total Sales' column fills with values: 5, 1.5, 24, 10.5, 6, 13.2, 12, 24, 15, 7.2

3

Step 3: Format the 'Total Sales' column as currency for better readability.

Select the 'Total Sales' column, then Format > Number > Currency.

Expected Result

All total sales values show with a currency symbol, e.g., $5.00, $1.50, $24.00, etc.

Final Result

Product     Quantity Sold  Unit Price  Total Sales
-------------------------------------------------
Apples      10             0.5         $5.00
Bananas     5              0.3         $1.50
Cherries    20             1.2         $24.00
Dates       7              1.5         $10.50
Elderberries3              2.0         $6.00
Figs        12             1.1         $13.20
Grapes      15             0.8         $12.00
Honeydew    8              3.0         $24.00
Indian Fig  6              2.5         $15.00
Jackfruit   4              1.8         $7.20

✓ARRAYFORMULA lets you calculate all total sales at once without copying formulas.

✓Total sales are correctly calculated by multiplying quantity and price for each product.

✓Formatting as currency makes the sales numbers easier to understand.

Bonus Challenge

Use ARRAYFORMULA to calculate a 10% sales tax for each total sale and add it as a new column.

Show Hint

Multiply the 'Total Sales' column by 0.10 inside an ARRAYFORMULA to get tax amounts for all rows.