A B

1 2

3 4

5 6

The values above represent 1 & 2 in cells A1:B1, 3 & 4 in cells A2:B2, 5 & 6 in cells A3:B3. I"m looking for the Maximum value of the sum of A1:B1, A2:B2 and A3:B3

You can use this formula =MAX(A1:A3+B1:B3). But after entering the formula press Ctrl+Shift+Enter instead of just Enter.

Unfortunately it does not work if function is in merged cells. Thanks anyway

Since now we know they are merged cells: the numbers 1 & 2 probably appear in only one cell, A1, B1 is blank

the numbers 3 & 4 probably appear in only one cell, A2, B2 is blank

the numbers 5 & 6 probably appear in only one cell, A3, B3 is blankWhich means, the numbers are actually, 12 twelve, 34 thirty four, and 56 fifty six

So, if this is true, then this formula should work:

=MAX(LEFT(A1:A3,1)+RIGHT(A1:A3,1))

It is also an ARRAY formula, so you must use CTRL-SHIFT-ENTER

EDIT ADDED:

Just re-read your reply, and you are correct that ARRAY formulas

will not work in merged cells.Here is a SUMPRODUCT() that seems to work:

=SUMPRODUCT(MAX(--LEFT({12;34;56},1)+(--RIGHT({12;34;56},1))))

MIKE

message edited by mmcconaghy

Ask Your Question

Weekly Poll

Do you think Slack's direct listing "IPO" is a good idea?

Discuss in The Lounge

Poll History