Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Mar 16 '13 at 6:58

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

2 Answers

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

share|improve this answer
    
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
add comment

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):

Using array formula to get maximum conditional sum

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.