Solved

# SUMPRODUCT

Posted on 2014-04-22
201 Views
Hi Experts,

I just need to know how do I retrieve numbers from its column  instead of values of SUMPRODUCT formula.
Column A numbers Column B Numbers.
Cell D3 using this formula =SUMPRODUCT(MAX((B1:B100)*(A1:A100)))
which returns to highest multiple value from column A & B.
But I want instead of value I want Column A Value.

See attached
Book1.xlsx
0
Question by:itjockey
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 76

Expert Comment

ID: 40014568
Do you mean like =(MAX(A1:A30)) ?
0

LVL 8

Author Comment

ID: 40014663
Do you mean like =(MAX(A1:A30)) ?

Nope bcoz say if A1:A30 have max value @ Cell A19 = 500 but if I change B7 from 7 to 2500 then my =SUMPRODUCT(MAX((B1:B100)*(A1:A100))) result would be 175000. which is maximum multiple value of column A & B.  I need pair not value in formula returns i.e. 70 (Which is A7) and 2500 (which is B7)which have maximum multiple value.

As per Attached file.

Thanks
0

LVL 50

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 40014666
Or do you mean

``````=SUMPRODUCT(A1:A30*(A1:A30*B1:B30=MAX(A1:A30*B1:B30)))
``````
Regards
0

LVL 50

Assisted Solution

Rgonzo1971 earned 500 total points
ID: 40014678
or

maybe this

``````=INDEX(A1:A30,MATCH(MAX(A1:A30*B1:B30),(A1:A30)*(B1:B30),0))
``````

As an array Formula

Regards
0

LVL 8

Author Closing Comment

ID: 40015048
Both are fine. it is exactly what i am looking for. if you feel to give some explanation on formulas....appreciated.

Thanks
0

LVL 50

Expert Comment

ID: 40015299
Personnaly I would use the second formual in case ther 2 time the maximum amount you would get the sum of both

in the second formula

(A1:A30)*(B1:B30) is the array of results

Match gives me the location of the first item that equals MAX(A1:A30*B1:B30) then with index I get the Nth of the array indicated in A1:A30

Regards
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question