Array formula to calculate sum product of every combination of rows from 2 ranges

[Kelvin Stott]

I have 2 ranges with different number of rows but same number of columns, for example:

Range1 = A1:Z10 =10 rows x 26 columns
Range2 = A11:Z30 = 20 rows x 26 columns

Now I need an array formula of 20 rows x 10 columns, which gives the sum product of every combination of rows from these two ranges (one entire row from each range). For example, the number in row r and column c of the array should be the sum product of entire row c from Range 1 and entire row r from Range 2.

Is there some array fomula using MMULT, TRANSPOSE, etc., which could create such an array?

Thanks for any help.

 

[Eric W]

This should give you an example, you'll need to update the ranges as necessary:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	2	3	4	5	6	7	8	9	10
2	2	3	4	5	6	7	8	9	10	11		55	65	75
3	3	4	5	6	7	8	9	10	11	12		110	130	150
4												165	195	225
5												220	260	300
6												275	325	375
7
8
9
10
11	1	1	1	1	1	1	1	1	1	1
12	2	2	2	2	2	2	2	2	2	2
13	3	3	3	3	3	3	3	3	3	3
14	4	4	4	4	4	4	4	4	4	4
15	5	5	5	5	5	5	5	5	5	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet10​

Worksheet Formulas Cell Formula L2 =SUMPRODUCT($A11:$J11,INDEX($A$1:$J$3,COLUMNS($L2:L2),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Kelvin Stott]

Not quite what I was looking for (single aray formula to calculate the entire array), but this works, thanks.

 

[DanteAmor]

You need something like this:

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:56.08px;" /><col style="width:56.08px;" /><col style="width:56.08px;" /><col style="width:56.08px;" /><col style="width:56.08px;" /><col style="width:56.08px;" /><col style="width:56.08px;" /><col style="width:56.08px;" /><col style="width:56.08px;" /><col style="width:62.73px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >AB</td><td >AC</td><td >AD</td><td >AE</td><td >AF</td><td >AG</td><td >AH</td><td >AI</td><td >AJ</td><td >AK</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td colspan="10" style="background-color:#00b0f0; text-align:center; ">Matriz 20 rows x 10 columns</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >By row 1</td><td >By row 2</td><td >By row 3</td><td >By row 4</td><td >By row 5</td><td >By row 6</td><td >By row 7</td><td >By row 8</td><td >By row 9</td><td >By row 10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#dbe5f1; text-align:right; ">6201</td><td style="background-color:#dbe5f1; text-align:right; ">6552</td><td style="background-color:#dbe5f1; text-align:right; ">6903</td><td style="background-color:#dbe5f1; text-align:right; ">7254</td><td style="background-color:#dbe5f1; text-align:right; ">7605</td><td style="background-color:#dbe5f1; text-align:right; ">7956</td><td style="background-color:#dbe5f1; text-align:right; ">8307</td><td style="background-color:#dbe5f1; text-align:right; ">8658</td><td style="background-color:#dbe5f1; text-align:right; ">9009</td><td style="background-color:#dbe5f1; text-align:right; ">9360</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >AB11</td><td >=SUMPRODUCT($A$1:$Z$1,$A11:$Z11)</td></tr><tr><td >AC11</td><td >=SUMPRODUCT($A$2:$Z$2,$A11:$Z11)</td></tr><tr><td >AD11</td><td >=SUMPRODUCT($A$3:$Z$3,$A11:$Z11)</td></tr><tr><td >AE11</td><td >=SUMPRODUCT($A$4:$Z$4,$A11:$Z11)</td></tr><tr><td >AF11</td><td >=SUMPRODUCT($A$5:$Z$5,$A11:$Z11)</td></tr><tr><td >AG11</td><td >=SUMPRODUCT($A$6:$Z$6,$A11:$Z11)</td></tr><tr><td >AH11</td><td >=SUMPRODUCT($A$7:$Z$7,$A11:$Z11)</td></tr><tr><td >AI11</td><td >=SUMPRODUCT($A$8:$Z$8,$A11:$Z11)</td></tr><tr><td >AJ11</td><td >=SUMPRODUCT($A$9:$Z$9,$A11:$Z11)</td></tr><tr><td >AK11</td><td >=SUMPRODUCT($A$10:$Z$10,$A11:$Z11)</td></tr></table></td></tr></table>

 

[DanteAmor]

This should give you an example, you'll need to update the ranges as necessary:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	2	3	4	5	6	7	8	9	10
2	2	3	4	5	6	7	8	9	10	11		55	65	75
3	3	4	5	6	7	8	9	10	11	12		110	130	150
4												165	195	225
5												220	260	300
6												275	325	375
7
8
9
10
11	1	1	1	1	1	1	1	1	1	1
12	2	2	2	2	2	2	2	2	2	2
13	3	3	3	3	3	3	3	3	3	3
14	4	4	4	4	4	4	4	4	4	4
15	5	5	5	5	5	5	5	5	5	5

<tbody>
</tbody>

Sheet10​

Worksheet Formulas Cell Formula L2 =SUMPRODUCT($A11:$J11,INDEX($A$1:$J$3,COLUMNS($L2:L2),0)) <tbody> </tbody>

<tbody>
</tbody>

Hi Eric, Sorry, I had not understood your formula, but I understood. It's much better than mine.

 

[Eric W]

You can do it with a single array formula:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	2	3	4	5	6	7	8	9	10
2	2	3	4	5	6	7	8	9	10	11		55	65	75
3	3	4	5	6	7	8	9	10	11	12		110	130	150
4	0	0	0	0	0	0	0	0	0	0		165	195	225
5	0	0	0	0	0	0	0	0	0	0		220	260	300
6												275	325	375
7
8												55	65	75
9												110	130	150
10												165	195	225
11	1	1	1	1	1	1	1	1	1	1		220	260	300
12	2	2	2	2	2	2	2	2	2	2		275	325	375
13	3	3	3	3	3	3	3	3	3	3
14	4	4	4	4	4	4	4	4	4	4
15	5	5	5	5	5	5	5	5	5	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet10​

Array Formulas Cell Formula L8:N12 {=MMULT(A11:J15,TRANSPOSE(A1:J5))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

But you'll need to create extra rows on the bottom to ensure both ranges are the same size.

 

[Kelvin Stott]

Oh perfect, thanks Eric.

But why do both ranges need to be the same size?

 

[Eric W]

Because that's how MMULT works.

https://support.office.com/en-ie/article/mmult-function-40593ed7-a3cd-4b6b-b9a3-e4ad3c7245eb

The number of columns in the first range must be the same as the number of rows in the second range. So when we are actually multiplying rows by rows, and making the second one into columns with TRANSPOSE, we have to make sure the rows match.

However, although the ranges must be defined as a particular size, it occurs to me that you don't actually have to use the results of those extra rows. For example, given your A1:Z10 and A11:Z30 ranges, you could use:

=MMULT(A11:Z30,TRANSPOSE(A1:Z20))

You will get 10 extra columns of unwanted data, but if you select a 10-column range to put the formula in, the last 10 columns won't show up anywhere.

Also note that MMULT requires numeric data, and empty cells will cause an error. If you have some empty rows, then you can change the formula to:

=MMULT(A11:Z30+0,TRANSPOSE(A1:Z20)+0)

and it will treat empty cells as 0. Character data will cause errors regardless though.

 

[Kelvin Stott]

Actually I just tried and it works even without the same number of rows (as long as you refer to the biggest range first), so this is perfect, EXACTLY what I was looking/hoping for!

Thanks again.

 

[Eric W]

Apparently I need to review my matrix math! Yes, it should work fine as you found out. Glad we could help.