An **array formula** is a special formula that is converted from a normal formula by the ** Ctrl+Shift+Enter** shortcut.

Excel automatically adds curly brackets “`{}`

” at the beginning and end of an array formula. The essence of the array formula is a cell formula, used to explicitly notify the Excel calculation engine to perform multiple calculations on it.

**Multiple calculations** are the process of performing the relevant calculations separately and simultaneously on the elements of an array that have the corresponding relationships in the formula.

However, not all formulas that perform multiple calculations must be edited into array formulas. Using arrays in the arguments of functions of array type or vector type that return a single result, these functions can automatically perform multiple calculations without using array formulas, such as the SUMPRODUCT function, LOOKUP function, MMULT function and MODE.

Table of Contents

## Multi-cell array formula

In a single cell using an **array formula** for **multiple calculations**, sometimes you can return a set of results, but the cell can only display a single value (usually the first element of the array results), and can not display the results of the entire group. Using multi-cell array formulas, you can display each element of the result array in a different cell.

### Multi-cell array formula example

Here is a sales table, if you want to calculate the sales volume of different salesmen, then you can multiply the unit price of `E3:E10`

by the quantity of `F3:F10`

.

The steps are as follows:

**STEP1#** Select the` G3:G10`

cell area, and enter the following formula in the formula bar (excluding the curly brackets on both sides)

`=E3:E10*F3:F10`

**STEP2#** Press ** Ctrl+Shift+Enter** shortcut keys to convert the formula into an array formula

**STEP3#** You can see that the sales volume of different salesmen can be calculated by an array formula.

This type of formula that uses the same formula in multiple cells and is converted by the** Ctrl+Shift+Enter** shortcut is called a “

**multi-cell array formula**“.

The above formula multiplies the unit price of each product by the respective sales quantity to obtain a memory array. The memory array is as below:

`{450;690;250;931;1131;1421;532;1862}`

Then, they are all displayed in the `G3:G10`

cell area.

**Note：**The curly brackets "**{}**" at the beginning and end of an array formula are automatically generated by the **Ctrl+Shift+Enter** shortcut. If you enter curly brackets manually, Excel will recognize them as text characters, and they will not work correctly as formulas.

## Single-cell array formula

**Single-cell array formulas **are array formulas that perform multiple calculations in a single cell and return a single value.

### Single-cell array formula example

If you want to calculate the total sales profit of all products, then you can use a single cell array formula to do the statistics.

You can use the following array formula in cell `G12`

, and then press ** Ctrl+Shift+Enter** to convert the formula to an array formula.

`=SUM(E3:E10*F3:F10)*G1`

The formula first multiplies the `unit price`

and `sales `

of each product, and then uses the `SUM function`

to add up all the elements in the array to get the `total sales`

. Finally, the total sales are multiplied by the `profit margin`

in cell `G1`

, and the returned value is the `total sales profit `

for all products.

As the parameters of the `SUM function`

are numeric types, they cannot directly support multiple calculations, so they must be in the form of an array formula to explicitly inform Excel to perform multiple operations.

The formula in this example can be replaced by the `SUMPRODUCT function`

:

`=SUMPRODUCT(E3:E10*F3:F10)*G1`

The parameters of the` SUMPRODUCT function`

are the array type, which directly supports multiple calculations, so you can enter the formula as a normal formula and get the correct result.

## Leave a Reply

You must be logged in to post a comment.