# Multiply All Numbers in Column by Each Other

#### texasfan1012

##### New Member
I have a column of numbers and need to multiply every number by all the other numbers in the column and then take the sum. Is there a way to do this using a formula and not a VBA/Paste Special Multiply?

For example, if the data set were the numbers below I would need:

5*7 + 5*8 + 5*4 + 5*9 + 7*8 + 7*4 + 7*9 + 8*4 + 8*9 + 4*9

 5 7 8 4 9

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

How can this be done using a formula so that I don't have to manually calculate this for a larger data set?

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
With my data in A2:A6 here is an array formula to give your result
To enter the formula with CTRL+SHIFT + ENTER, not just ENTER.
Excel will enclose the formula in {}. Do not type these yourself

=(SUMPRODUCT(--(A2:A6)*(TRANSPOSE(A2:A6)))-SUMPRODUCT(A2:A6*A2:A6))/2

Every number in the sequence needs to be multiplied by all of the numbers below it. The final solution I am looking for is the sum of all the pairs multiplied

Did you test my formula? That is what it does.

Column A below is what you have/need, and the rest of the sheet is my proofing

Excel Workbook
ABCDEF
157849
252535402045
373549562863
484056643272
542028321636
694563723681
7
8427
9
10
11427
Sheet12

Every number in the sequence needs to be multiplied by all of the numbers below it. The final solution I am looking for is the sum of all the pairs multiplied

Excel 2010
BCDEFGHIJKLM
2Cum:255
3Cnt:5555
5Inputs:555840
6774520
7885945
84477
9997856
104728
117963
1288
134832
14#8972
151555544
16257574936
173585899
1845445427
1955959
2067557
2177777
2287878
2397447
24107979
25118558
26128778
27138888
28148448
29158989
30164545
31174747
32184848
33194444
34204949
35219559
36229779
37239889
38249449
39259999
using Formulas
Cell Formulas
RangeFormula
C2=PRODUCT(relS:\$D\$3)
C3=COUNTA(Symbols)
C15=INDEX(Symbols, MOD(INT((Line - 1) * Cnt / Cum), Cnt) + 1)
D2=PRODUCT(relS:\$D\$3)
D3=COUNTA(Symbols)
D15=INDEX(Symbols, MOD(INT((Line - 1) * Cnt / Cum), Cnt) + 1)
M4=K4*L4
M18=SUM(M3:M17)
B15=ROW()-ROW(B\$14)
F15=MIN(C15:D15)
G15=MAX(C15:D15)
Named Ranges
NameRefers ToCells
'using Formulas'!Cnt='using Formulas'!B\$3
'using Formulas'!Cum='using Formulas'!B\$2
'using Formulas'!Line='using Formulas'!\$B2
'using Formulas'!relS='using Formulas'!B3
'using Formulas'!Symbols=INDEX('using Formulas'!B\$4:B\$14, 2):INDEX('using Formulas'!B\$4:B\$14, ROWS('using Formulas'!B\$4:B\$14) - 1)

which matches konew1's formula

Last edited:
<b-:

which matches konew1's formula

Yes, the result matches, but I think the data in the original posting is a restricted size example only. With a large data range I would stick to a single cell solution.

With a large data range I would stick to a single cell solution.

Except if you wish to see the components as well as the total....

Except if you wish to see the components as well as the total....

@konew1 tested your formula with a bigger data set and it works perfect.

Thank you both for the help

Pleased to help. Thanks for the ackn

Replies
2
Views
113
Replies
16
Views
517
Replies
3
Views
226
Replies
4
Views
70
Replies
0
Views
42

1,203,486
Messages
6,055,701
Members
444,809
Latest member
mwh85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back