SUM MAX accross columns

[manywise]

Hi,
I have anexcel file with data of type:
500 200 2000
1000 0 6000
ranging from cell C7 to AL37.

I have been trying to return the maximum of two cells (same column, different rows), add the results and return in a different ceel e.g. if i consider the sample above then i will like it to return:
1000 + 200 + 6000.
I tried to use
SUM(MAX(C7;C8):MAX(AL7:AL8) to return the reults for two rows, but it only compute the first and last column. Is there a way i can compute all the columns without having to explicitly specify them out?

Thanks in Advance
Manywise

[daddylonglegs]

You can use an "array formula" like this

=SUM(IF(C7:AL7>C8:AL8,C7:AL7,C8:AL8))

confirmed with CTRL+SHIFT+ENTER

or a "regular" formula

=SUMPRODUCT(SUBTOTAL(4,OFFSET(C7:C8,0,COLUMN(C7:AL7)-COLUMN(C7))))

[manywise]

Thanks a lot, this seems to have some major key issues:
Applying the two formulaes to a small data set as shown below:
rows 7 to 9, columns C to E

1000 1500 500
500 600 1000
0 1000 0

Array formula
=SUM(IF($C$7:$AL$7>C8:AL8;$C$7:$AL$7;C8:AL8))
Results: 2100, 3000
Regular formula
SUMPRODUCT(SUBTOTAL(4;OFFSET($C$7:C8;0;COLUMN($C$7:$AL$7)-COLUMN($C$7))))
Results: 3000, 3000

The right answer is 1000 + 1500 + 1000= 2500 for rows 7 and 8 and 1000+1500+500=3000 for rows 7 and 9.

I have used "$" to keep keep a fix row and calculate the sum max for the combination of that row with all the other rows by using excel drag/drop function. I am using microsoft office excel 2007 hence i changed "," to ";" from the formula you suggested.

Is there anyone who can please help me with what is going on, and what is the right thing for m e to do?
Thanks
Manywise

[DonkeyOte]

The correct answer for row 7 to 8 is 3500 and that is what daddylonglegs' formulae will return
we are assuming of course you confirmed the first as an Array using CTRL + SHIFT + ENTER (enter alone will not suffice)

If you wish to compare row 7 to row 9 (ie non contiguous range) then you will need to adjust the references in the Array to row 8 accordingly:

Please Login or Register  to view this content.

The SUMPRODUCT would prove cumbersome to adjust for non-contiguous ranges.

[manywise]

Thanks, your advice does the magic, it's absolutely necessary to confirm CTRL + SHIFT + ENTER in every cell to get the right output.
\Manywise

[manywise]

Hi,
Anyone has an idea of how i can achieve the following:

Using something like
=(ROW(A2), COLUMN(B1))

I will like it to return (content of row A2, content of column B1).

In this particular case the contents are numbers i.e. (1,2).

The target cell should return (1,2) in parenthesis.

BR
Manywise

[DonkeyOte]

Not very clear... perhaps you want:

Please Login or Register  to view this content.

Re: above...

A cell can only ever present a single value - if you use an Array to generate the two different values, ie:

Please Login or Register  to view this content.

the cell containing the above will only ever display content of A2 (though the underlying Array holds both values)

[ray_shaw]

why don't you pull out the max for each column first....and stick them somewhere else on your spreadsheet ...then add them together in another cell

Thanks a lot, this seems to have some major key issues:
Applying the two formulaes to a small data set as shown below:
rows 7 to 9, columns C to E

1000 1500 500
500 600 1000
0 1000 0

Array formula
=SUM(IF($C$7:$AL$7>C8:AL8;$C$7:$AL$7;C8:AL8))
Results: 2100, 3000
Regular formula
SUMPRODUCT(SUBTOTAL(4;OFFSET($C$7:C8;0;COLUMN($C$7:$AL$7)-COLUMN($C$7))))
Results: 3000, 3000

The right answer is 1000 + 1500 + 1000= 2500 for rows 7 and 8 and 1000+1500+500=3000 for rows 7 and 9.

I have used "$" to keep keep a fix row and calculate the sum max for the combination of that row with all the other rows by using excel drag/drop function. I am using microsoft office excel 2007 hence i changed "," to ";" from the formula you suggested.

Is there anyone who can please help me with what is going on, and what is the right thing for m e to do?
Thanks
Manywise