Select top 5 in column A, sum values in B
 

Hi,
I have a formula that finds and sums the top 5 values in column A for a
given range:

SUM(LARGE($A$3:$A$500,ROW(INDIRECT("1:5"))))

Now I want to use the top 5 criteria in column A to sum corresponding
cells in B.
I tried incorporating into this formula:

SUM(IF(A3:A500=LARGE(A3:A500,ROW(INDIRECT("1:5"))) ,B3:B19))

but that's not it.
Any help would be appreciated!
Phillycheese5


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=378092

Phillycheese5 Wrote:
I have a formula that finds and sums the top 5 values in column A for a
given range:

SUM(LARGE($A$3:$A$500,ROW(INDIRECT("1:5"))))

This formula can be changed to eliminate the need to confirm with
CONTROL+SHIFT+ENTER...


Code:
--------------------
=SUM(LARGE($A$3:$A$500,{1,2,3,4,5}))

...confirmed with just ENTER.
--------------------


Now I want to use the top 5 criteria in column A to sum corresponding
cells in B.
I tried incorporating into this formula:

SUM(IF(A3:A500=LARGE(A3:A500,ROW(INDIRECT("1:5"))) ,B3:B19))

Try...


Code:
--------------------
=SUMPRODUCT(($A$3:$A$500=(LARGE($A$3:$A$500,{1,2,3 ,4,5})))*$B$3:$B$500)

...confirmed with just ENTER
--------------------


Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=378092

How about...

=SUM(IF(A3:A500LARGE(A3:A500,6),B1:B10))

Array-entered.

HTH
Jason
Atlanta, GA

"Phillycheese5" wrote:


Hi,
I have a formula that finds and sums the top 5 values in column A for a
given range:

SUM(LARGE($A$3:$A$500,ROW(INDIRECT("1:5"))))

Now I want to use the top 5 criteria in column A to sum corresponding
cells in B.
I tried incorporating into this formula:

SUM(IF(A3:A500=LARGE(A3:A500,ROW(INDIRECT("1:5"))) ,B3:B19))

but that's not it.
Any help would be appreciated!
Phillycheese5


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=378092

Sorry, B1:B10 should be B3:B500.

Jason

"Jason Morin" wrote:

How about...

=SUM(IF(A3:A500LARGE(A3:A500,6),B1:B10))

Array-entered.

HTH
Jason
Atlanta, GA

"Phillycheese5" wrote:


Hi,
I have a formula that finds and sums the top 5 values in column A for a
given range:

SUM(LARGE($A$3:$A$500,ROW(INDIRECT("1:5"))))

Now I want to use the top 5 criteria in column A to sum corresponding
cells in B.
I tried incorporating into this formula:

SUM(IF(A3:A500=LARGE(A3:A500,ROW(INDIRECT("1:5"))) ,B3:B19))

but that's not it.
Any help would be appreciated!
Phillycheese5


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=378092

The formula you have to sum the top 5 values in column A sums exactly N
(5) largest values. The question which now arises can be shown with a
small sample in A1:B5 with Top N set to 3:

A1: 5, B1: 10
A2: 7, B2: 10
A3: 7, B3: 8
A4: 8, B4: 10
A5: 9, B5: 10

Sum of the exactly/strictly 3 largest from A is given by the type
formula you used:

[A]

{=SUM(LARGE($A$1:$A$5,ROW(INDIRECT("1:3"))))

which is: 24.

You state: "Now I want to use the top [3] criteria in column A to sum
corresponding cells in B."

What should be the result, given the above sample?

[1] 28 or

[2] 30 or

[3] 38?

A choice between [1] and [2] looks quite arbitrary, while [3] conraticts
the intent/purpose of formula [A].

Phillycheese5 wrote:
Hi,
I have a formula that finds and sums the top 5 values in column A for a
given range:

SUM(LARGE($A$3:$A$500,ROW(INDIRECT("1:5"))))

Now I want to use the top 5 criteria in column A to sum corresponding
cells in B.
I tried incorporating into this formula:

SUM(IF(A3:A500=LARGE(A3:A500,ROW(INDIRECT("1:5"))) ,B3:B19))

but that's not it.
Any help would be appreciated!
Phillycheese5