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))))
message edited by mmcconaghy
Yes (14) | ![]() | |
No (14) | ![]() | |
I don't know (15) | ![]() |