Is there a concise Excel formula to calculate (A1*A6)+(B1*B6)+(C1*C6)...?
You are looking for the SUMPRODUCT function.
=SUMPRODUCT(A1:C1,A6:C6)
This will return the sum of the products of corresponding items in two (or more) ranges.
As you can see from the Microsoft documentation I linked to, the ranges don't need to be single rows or single columns (although they must have the same dimensions).
SUMPRODUCT can multiply values from up to 255 different ranges. For example =SUMPRODUCT(A1:C1,A6:C6,A11:C11) is the same as =A1*A6*A11+B1*B6*B11+C1*C6*C11.
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
Another approach is to put in A7 the expression =A1*A6 and copy right as far as you want, then sum row $7$ to get the final answer. It does not do it in one cell like you want, but sometimes having the intermediate products is handy. I have used both versions. This one feels more Excel-ish to me, but your taste may differ.