46

I want to know how to give a concise formula for the following long formula in Excel sheet:

= (A1*A6)+(B1*B6)+(C1*C6)...

Do I use a sum function or is there another function for this? I think using the colon with SUM function should help but I don't know how to use it.

4

5 Answers 5

109

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.

enter image description here

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.

20

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

6
  • 7
    However, be aware that array functions are very fragile. If you copy and paste them, for example, things can break really badly. Frequently, they won't be updated correctly without a forced update if they're very large. Great, powerful feature, but persnickety compared to regular formulas.
    – JKreft
    Aug 19, 2018 at 22:07
  • 3
    @StianYttervik: I think the answer is useful. What if someone opens a sheet with this kind of formula and has never seen it before? This answer would be helpful for those users.
    – user531168
    Aug 20, 2018 at 9:44
  • 3
    @StianYttervik array formula is a powerful tool in the correct hand. It isn't slow at all, since it can be easier optimized and vectorized. And it saves memory. Even google docs support it. And don't reply to this. I won't respond any further
    – phuclv
    Aug 20, 2018 at 14:46
  • 2
    @StianYttervik Certain functions (e.g. MOD) will cause SUMPRODUCT to break, and then you have to replace it with an Array formula. Not to mention every version of Office released has yet-more commonly used Array Formulae added as official functions (e.g. MAXIFS in 365) If your array formula is slow, clunky, and breaking, you probably need to optimise your code. And, of course - since PowerQuery is an additional Add-In Extension, many Companies will (for various reasons) not include it in their Corporate build package, making it an inferior solution for distributed Business purposes. Aug 20, 2018 at 22:17
  • 1
    @Chronocidal Yes, there are cases, but they are few... And then power queries are the solution. Or the data model. Or Cube functions. Or all the above. Arrays and the row by row calculation/execution just can't measure up.
    – Stian
    Aug 21, 2018 at 5:14
7

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.

1

if you put list of numbers in columns instead of rows(say two columns A and B), you can use the function =Sumproduct(A:A,B:B) like that. it will give you the product of as many numbers as there are in A and B columns.

You can use as many columns in Sumproduct function as you need

1
  • 2
    Sumproduct(1:1,2:2) works the same way for rows too. Aug 20, 2018 at 17:02
0

If there is nothing else in rows 1 and 6 other than what you want to have a SUMPRODUCT() for, you could use the idea mentioned in this comment. In your case as outlined in your question you would use =SUMPRODUCT(1:1,6:6)

2
  • 4
    This is not really giving additional information on top of what's already entered in answers and comments. Aug 21, 2018 at 8:36
  • 1
    @MátéJuhász - What answer provides my answer? Your comment to the answer provided by @AjayC gives SUMPRODUCT(1:1,2:2) and I provided an answer based on that comment elaborating how it can be used based on the original question and what the limitation to that would be. Aug 21, 2018 at 8:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .