How to do ProductSum

chungyin383

New Member
How to evaluate this sum with one formula?
$image=https://latex.codecogs.com/gif.latex?%5Cprod_%7Bi%3D1%7D%5E%7Bm%7D%5Csum_%7Bj%3D1%7D%5E%7Bn%7Da_%7Bij%7D&hash=26cc64f5481fc353a217ea42352216d7$

Example:

1 2 3
4 5 6
7 8 9

The formula should return 3240.

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Gravitas

Board Regular
=SUMPRODUCT(1+2*(ROW(INDIRECT("1:"&\$A\$1))-1))

Hope it helps you.

chungyin383

New Member
=SUMPRODUCT(1+2*(ROW(INDIRECT("1:"&\$A\$1))-1))

Hope it helps you.

Thanks but it seems that your formula does not do what I want...
Let me illustrate what I wish to achieve. In the matrix in my example, the column sums are 12, 15, 18 respectively. The product is therefore 3240. Can I use one formula to give 3240 directly?

MrExcel MVP
Thanks but it seems that your formula does not do what I want...
Let me illustrate what I wish to achieve. In the matrix in my example, the column sums are 12, 15, 18 respectively. The product is therefore 3240. Can I use one formula to give 3240 directly?

Let I1:K3 house the data.

Control+shift+enter, not just enter:

=PRODUCT(MMULT(TRANSPOSE(I1:K3),ROW(I1:K3)^0))

chungyin383

New Member
Let I1:K3 house the data.

Control+shift+enter, not just enter:

=PRODUCT(MMULT(TRANSPOSE(I1:K3),ROW(I1:K3)^0))

Thanks man! Haven't thought of the use of matrix multiplication in this problem

MrExcel MVP
Thanks man! Haven't thought of the use of matrix multiplication in this problem

You are welcome and a happy new year.

Replies
0
Views
128
Replies
7
Views
208
Replies
6
Views
288
Replies
2
Views
148
Replies
10
Views
525

1,191,694
Messages
5,988,141
Members
440,129
Latest member
bianca88

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.

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