Solved

SUMPRODUCT

Posted on 2014-04-22
6
196 Views
Last Modified: 2014-04-22
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
Comment
Question by:itjockey
  • 3
  • 2
6 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40014568
Do you mean like =(MAX(A1:A30)) ?
0
 
LVL 8

Author Comment

by:itjockey
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 49

Accepted Solution

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

=SUMPRODUCT(A1:A30*(A1:A30*B1:B30=MAX(A1:A30*B1:B30)))

Open in new window

Regards
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 500 total points
ID: 40014678
or

maybe this

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

Open in new window


As an array Formula

Regards
0
 
LVL 8

Author Closing Comment

by:itjockey
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 49

Expert Comment

by:Rgonzo1971
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now