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
There are many better solutions. One way is to use array formula. You can see an exact example like yours over there:
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:
To use array formula in your case you can type (of course you need to change the last element of the array accordingly)
and then press Ctrl+Shift+Enter
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