Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

SUMPRODUCT

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
Naresh Patel
Asked:
Naresh Patel
  • 3
  • 2
2 Solutions
 
GrahamSkanRetiredCommented:
Do you mean like =(MAX(A1:A30)) ?
0
 
Naresh PatelTraderAuthor Commented:
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
 
Rgonzo1971Commented:
Or do you mean

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

Open in new window

Regards
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Rgonzo1971Commented:
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
 
Naresh PatelTraderAuthor Commented:
Both are fine. it is exactly what i am looking for. if you feel to give some explanation on formulas....appreciated.

Thanks
0
 
Rgonzo1971Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now