# MAX of SUM of Cells with Line Conditions

I want to find the Maximum of a Sum of 2 Cells in a Line where in Column B is a specific identifying string. On this

``````A           B           C           D

1           Sx          7           1
2           Sx          3           6
3           Eur         3           5
4           AK1         4           5
5           AK1         4           5
``````

when searching for Sx it should return 9. The search string is specified in a named range of the dimension 1x1 (one cell). I cant figure out a way to do this with the standard Excel functions.

-

## migrated from stackoverflow.comMar 16 '13 at 6:58

This question came from our site for professional and enthusiast programmers.

The easy answer is to add another column (E) to sum C and D and then use an array formula like

`=MAX(IF(B1:B5="Sx",E1:E5))`

confirmed with CTRL+SHIFT+ENTER

or without a helper column you can use this "array formula"

`=MAX(IF(B1:B5="Sx",SUBTOTAL(9,OFFSET(C1:D5,ROW(C1:D5)-ROW(C1),0,1))))`

.....or another array formula option.....

`=MAX(IF(B1:B5="Sx",MMULT(C1:D5,{1;1})))`

for that to work C1:D5 must be fully populated with numbers

-
Your first approach is working great. Thanks for that. Follow up question is: How can I clone other cells in the same line where the MAX is found or the whole line? – Higgns Mar 19 '13 at 20:10

You could also try this array formula:

``````=MAX((B1:B5=identifier)*(C1:C5+D1:D5))
``````

Where:

`identifier` points to a cell that contains your search string

Example / Result (formula entered in cell `H2`):

Don't forget to commit it using Ctrl + Shift + Enter.

-