Home » Blog » Multiply two columns and sum

Multiply two columns and sum

Hello everyone! Did you know…We have at least two ways in Microsoft Excel to multiply two columns and sum.

click to INTERACT

Example numbers for our multiply two columns and sum

Say we have two sets of numbers in two different columns. The first in range C5 to C7 and they are 5, 8 and 6. The second range is adjacent and is D5 to D7 with 2, 3 and 1.

The long way

In cell E5 we enter =C5*D5 and double click quickly the Fill Handle once we reselect cell E5.  The Fill Handle is located at the bottom right corner of cell E5 when the cell is selected. We get answers 10, 24 and 6.

Then in cell E9 we enter =SUM(E5:E7). The answer is 40.

A better way?

A quicker way to get the same answer might be to use an array formula and do everything in a single cell. So in cell D9 we could enter the formula =SUM(C5:C7*D5:D7) and press CTRL + SHIFT + ENTER to enter it turning it into an array formula with curly brackets, i.e. {=SUM(C5:C7*D5:D7)}.

How it works

We get the same result or answer to the long way, i.e. 40. This is because this array formula iterates through, in this case, the three array multiplication combinations and then sums the three results up.

Sometimes it’s nice to take shortcuts!

Additional Info

The Table option

Another way to multiply two columns and sum is the Table way.  Click a cell in our data range and convert it to a Table by using the keyboard shortcut CONTROL + t, ticking “My table has headers”.  When you go to the adjacent column on the right, just enter the formula by pointing and clicking on the cells to be used in the first multiplication formula.  The formula will look something like this:

=[@column name 1]*[@column name 2]

This automatically copies down the column itself and the formula stays the same.  “[” and “]” denotes column.  The “@” symbol here just means current row and so there is a different current row for each formula.  This differentiates one formula from the next even though they all appear to be the same formula!

For our total then, just click the check mark for Total Row which you will find in the Table Style Options group on the Design tab of Table Tools.

Get spreadsheet

click to INTERACT

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.