Solved

SUMPRODUCT

Posted on 2014-04-22
6
201 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
[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
  • Learn & ask questions
  • 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 50

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

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 50

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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