Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

migrated from Mar 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


confirmed with CTRL+SHIFT+ENTER

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


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


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

You could also try this array formula:



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

Your Answer


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.