Array Formulas

Hi Charlette

Here's an example where an array formula is used.

Imagine you have a list of sales figures with 3 columns - Salesperson, units sold and Price.

Salesperson Sold Price
Barnhill 5 2200
Belcher 4 1800
Ingle 6 2300
Jordan 3 2000
Pike 9 2150
Sancher 6 2250

The company owner wants to calculate total sales in a single cell.
Instead of entering a formula for Units * Price , copying it down and then totalling the results, you can use an array formula:

=SUM(B2:B7*C2:C7)

Then press Ctrl+Shift+Enter to make it an array formula.
This adds curly brackets around the formula.

{=SUM(B2:B7*C2:C7)}

This is an example of a singe-cell array formula. It multiplies a range by another range one row at a time. By having just one cell it can save space and avoid errors caused by multiple formulas.

I hope that helps. More about arrays is included in the Excel Advanced for Power Users course.

Regards
Doug Dunn
Best STL