Excel horizontal only sum of an array (matrix)

I have a formula which generates a 2D array result. For example:

{=ROW(4:6)^TRANSPOSE(ROW(1:3))}

(the curly brackets are added by excel when you hit CTRL+SHIFT+ENTER )

I need to sum only the horizontal elements of the array (then I will determine the maximum of these sums).

If I try:

{=SUM(ROW(4:6)^TRANSPOSE(ROW(1:3)))}

I get the total sum, but what I need is an array formula which generates an array result containing a (vertical) column of the sum of each row. I imagine something like:

{=HORIZONTALSUM(ROW(4:6)^TRANSPOSE(ROW(1:3)))}

Which would, in my simple example, contain a vertical array of (24;30;36) Then I could get the maximum easily with:

{=MAX(HORIZONTALSUM(ROW(4:6)^TRANSPOSE(ROW(1:3))))}

And return 36 as in my example case.

I realise I could write a UDF to do this easily but UDF's are not portable enough so I'm looking for a native solution.

Mr Purple

Posted 2015-02-24T22:17:08.663

Reputation: 305

Answers

Does this do what you want?

=MAX(ROW(4:6)*SUM(ROW(1:3)))

confirm with CTRL+SHIFT+ENTER

If you sum the second array you can use that to multiply the first array because that will be the same as multiplying the values individually and then summing the results

Edit: given your comments if the initial array is fixed then you can use MMULT function like this

=MAX(MMULT(ROW(4:6)*TRANSPOSE(ROW(1:3)),{1;1;1}))

the {1;1;1} would change given the number of columns in the first array

barry houdini

Posted 2015-02-24T22:17:08.663

Reputation: 10 434

No. My example array formula is just to easily provide an array. My actual formula is complicated and cannot be split up in this manner. It's the first thing I looked at trying too. I will try to generate an example array which clearly has the same limitations. – Mr Purple – 2015-02-24T22:39:29.477

I have now edited the example formula so it more accurately represents the essence of the limitations I have to deal with. – Mr Purple – 2015-02-24T22:46:02.703

I edited my answer too....... – barry houdini – 2015-02-24T22:47:45.167

Nice. I see that instead of {1;1;1} I could do (ROW(1:N)^0) where N is the number of rows I want to sum accross. – Mr Purple – 2015-02-24T22:54:43.473

Please note that "rows in the first array" corrosponds to columns the the array we wish to sum horizontally. Also if using the ROW(1:N)^0 formula you will need to use INDIRECT if you want to change the number of N dynamically using the value in a cell reference. ie ROW(INDIRECT("1:"&N))^0) where N is a reference or formula containing the number of columns you want to sum across – Mr Purple – 2015-02-25T02:58:48.433

Yes, sorry, that was wrong - the number of 1s in the {1;1;1} part is the same as the number of columns in the first array, as you say - I edited my answer – barry houdini – 2015-02-25T08:15:21.053

Post-multiply your matrix by a column-vector of ones:

{=MMULT(themat,{1;1;1})}

where themat is a named reference for your matrix.

Hat tip to:

https://stackoverflow.com/questions/50565859/sum-rows-or-columns-of-a-2d-matrix-into-a-1d-array-in-an-excel-formula

PS: the exemple given in the question does not run in Excel.

PPS: essentially the same solution was given in a previous answer, although the essence of it was buried in an attempt to clarify the OP's input.

Felipe G. Nievinski

Posted 2015-02-24T22:17:08.663

Reputation: 113