CODEWITHSUNDEEP
excelsumformulaexcel-formula
Tauren
Tauren

Reputation: 37

How to return a value in a column which has the maximal sum in another column?

    A       B
1   #       $   
2   1001    10  
3   1003    20  
4   1004    10  
5   1001    20  
6   1001    10  
7   1005    20  
8   1003    10  
9   1004    20

I want the # in column A whose has the max $ sum in column B and I want the result in cell A11

The result should be 1001

Thanks a million for any contribution!

Upvotes: 0

Views: 267

Answers (2)

JMax
JMax

Reputation: 26591

Here is a first try:

{=MIN(IF(B2:B9=MAX(B2:B9),A2:A9,MAX(A2:A9)))}

This is an array formula you have to validate with Ctrl+Shift+Enter

This formula will return the minimum value of # which correspond to the max value of $ in the column B.
I chose to return the min value of # to return 1001 as described in your question as the expected output.

Upvotes: 1

barry houdini
barry houdini

Reputation: 46341

I assume the answer is 1001 because 1001 has the highest total (40) taking into account all entries in the data. To get that result use this "array formula"

=INDEX(A2:A9,MATCH(MAX(SUMIF(A2:A9,A2:A9,B2:B9)),SUMIF(A2:A9,A2:A9,B2:B9),0))

confirmed with CTRL+SHIFT+ENTER

If you want to avoid complex array formulas like that then use a helper column, i.e. in C2 copied down

=SUMIF(A$2:A$9,A2,B$2:B$9)

then for your end result

=INDEX(A2:A9,MATCH(MAX(C2:C9),C2:C9,0))

Upvotes: 3

Related Questions