Multiply All Numbers in Column by Each Other

[texasfan1012]

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?

 

[konew1]

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

 

[sheetspread]

If you would like to see the pairs as you multiply and add them:

http://www.mrexcel.com/forum/lounge-v-2-0/856840-how-many-combinations.html (#7 and #8)

 

[texasfan1012]

If you would like to see the pairs as you multiply and add them:

http://www.mrexcel.com/forum/lounge-v-2-0/856840-how-many-combinations.html (#7 and #8)

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

 

[konew1]

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
	A	B	C	D	E	F
1		5	7	8	4	9
2	5	25	35	40	20	45
3	7	35	49	56	28	63
4	8	40	56	64	32	72
5	4	20	28	32	16	36
6	9	45	63	72	36	81
7
8		427
9
10
11	427
Sheet12

 

[sheetspread]

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
	B	C	D	E	F	G	H	I	J	K	L	M
2	Cum:	25	5
3	Cnt:	5	5							5	5
4	Header:	1	2							5	7	35
5	Inputs:	5	5							5	8	40
6		7	7							4	5	20
7		8	8							5	9	45
8		4	4							7	7
9		9	9							7	8	56
10										4	7	28
11										7	9	63
12										8	8
13										4	8	32
14	#									8	9	72
15	1	5	5		5	5				4	4
16	2	5	7		5	7				4	9	36
17	3	5	8		5	8				9	9
18	4	5	4		4	5						427
19	5	5	9		5	9
20	6	7	5		5	7
21	7	7	7		7	7
22	8	7	8		7	8
23	9	7	4		4	7
24	10	7	9		7	9
25	11	8	5		5	8
26	12	8	7		7	8
27	13	8	8		8	8
28	14	8	4		4	8
29	15	8	9		8	9
30	16	4	5		4	5
31	17	4	7		4	7
32	18	4	8		4	8
33	19	4	4		4	4
34	20	4	9		4	9
35	21	9	5		5	9
36	22	9	7		7	9
37	23	9	8		8	9
38	24	9	4		4	9
39	25	9	9		9	9
using Formulas

Cell Formulas
Range	Formula
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
Name	Refers To	Cells
'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

 

[konew1]

<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.

 

[sheetspread]

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....

 

[texasfan1012]

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

 

[konew1]

Pleased to help. Thanks for the ackn