`SUM`

function won't work since it just adds the elements. You need to multiply the values *before* passing to SUM like `=SUM(A1*A6, B1*B6, C1*C6, D1*D6, E1*E6)`

Of course you can also use `=A1*A6 + B1*B6 + C1*C6 + D1*D6 + E1*E6`

which results in pretty much the same typing effort as `SUM`

There are many better solutions. One of them is already suggested by Blackwood. Another alternative way is to use array formula. You can see an example exactly the same as yours from Microsoft:

# Array formula syntax

In general, array formulas use standard formula syntax. They all begin with an equal (=) sign, and you can use most of the built-in Excel functions in your array formulas. The key difference is that when using an array formula, you press `Ctrl`+`Shift`+`Enter` to enter your formula. When you do this, Excel surrounds your array formula with braces — if you type the braces manually, your formula will be converted to a text string, and it won't work.

Array functions are a really efficient way to build a complex formula. The array formula `=SUM(C2:C11*D2:D11)`

is the same as this:

```
=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).
```

To use array formula in your case you can type (of course you need to change the last element of the array accordingly)

```
=SUM(A1:E1*A6:E6)
```

and then press `Ctrl`+`Shift`+`Enter`

Once you grasp the idea of array formula it can be applied to most of other formulas and you can even forget the existence of `SUMPRODUCT`

# Update:

Newer Excel versions will even automatically use array formulas in many cases

Beginning with the September 2018 update for Office 365, any formula that can return multiple results will automatically spill them either down, or across into neighboring cells. This change in behavior is also accompanied by several new dynamic array functions. Dynamic array formulas, whether they’re using existing functions or the dynamic array functions, only need to be input into a single cell, then confirmed by pressing Enter. Earlier, legacy array formulas require first selecting the entire output range, then confirming the formula with `Ctrl`+`Shift`+`Enter`. They’re commonly referred to as CSE formulas.

Guidelines and examples of array formulas

Array formula is a very powerful tool. However use it with care. Every time you need to edit it you must not forget to press `Ctrl`+`Shift`+`Enter`

# Why use array formulas?

If you have experience using formulas in Excel, you know that you can perform some fairly sophisticated operations. For example, you can calculate the total cost of a loan over any given number of years. You can use array formulas to do complex tasks, such as:

Count the number of characters that are contained in a range of cells.

Sum only numbers that meet certain conditions, such as the lowest values in a range or numbers that fall between an upper and lower boundary.

Sum every nth value in a range of values.

# Array formulas also offer these advantages:

**Consistency:** If you click any of the cells from E2 downward, you see the same formula. That consistency can help ensure greater accuracy.

**Safety:** You cannot overwrite a component of a multi-cell array formula. For example, click cell E3 and press Delete. You have to either select the entire range of cells (E2 through E11) and change the formula for the entire array, or leave the array as is. As an added safety measure, you have to press `Ctrl`+`Shift`+`Enter` to confirm the change to the formula.

**Smaller file sizes:** You can often use a single array formula instead of several intermediate formulas. For example, the workbook uses one array formula to calculate the results in column E. If you had used standard formulas (such as =C2*D2, C3*D3, C4*D4…), you would have used 11 different formulas to calculate the same results.

It's also faster since the access pattern is already known. Now instead of doing 11 different calculations separately, it can be vectorized and done in parallel, utilizing multiple cores and SIMD unit in the CPU