Getting a the max value of an array of the sums of multiple columns? Is it possible?

[drew.j.harrison]

Having a hard time thinking of a method of doing this within a single cell. Hopefully you guys/gals can help. Basically what I am looking to do is find a way to create a dynamic array of the sums of individual columns and return the maximum value in that array. Below is the best I could come up with but it is only dynamic in height. I would like the quantity of columns to also be dynamic. The example code below is using a range of A1:E5. An example would be if each column is a Day and each row is a quantity I want to know what the maximum total quantity for a single day is.

Please Login or Register  to view this content.

The easy way would be to have an additional row of totals and which have a dynamic height [i.e. =sum(offset(A1,0,0,Num_Of_Rows))] and then use a separate cell to set a dynamic width across the total row for the maximum [i.e. =max(offset(A6,0,0,0,Num_Of_Columns))] but I don't have the ability to have a totals row here.

Hopefully there is another solution you can help me find. Thanks.

FYI this has been crossposted here:
Getting a the max value of an array of the sums of multiple columns? Is it possible?

[WHER]

Something along these lines (ctrl-shift-enter)

Please Login or Register  to view this content.

[JBeaucaire]

Building on WHER's idea, if you want the number of columns to adjust themselves, let's assume row1 always has a value in every used column, then this version:

=MAX(SUBTOTAL(9, OFFSET(A1,0, COLUMN(OFFSET(A1, , , , COUNTA(1:1)))-1, Num_of_Rows, 1)))

...also with CTRL+SHIFT+ENTER.

[drew.j.harrison]

Never did post an update here but this was solved on the cross-posted thread with the following code:

{=MAX(MMULT(TRANSPOSE($B$2:$F$17)+0,ROW($A$2:$A$17)^0))}

Thanks for the help!

[Tony Valko]

Nice one!

And if you want the max sum of rows:

Data Range

	A	B	C	D	E	F
5	42	1	7	84		134
6	58	94	84	22		258
7	52	8	18	78		156
8	30	40	6	59		135
9	36	14	50	9		109
10	36	31	82	75		224
11	------	------	------	------	------	------

Array entered**:

=MAX(MMULT(A5:D10,TRANSPOSE(COLUMN(A5:D5)^0)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.