Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
splitting the values from one column into two | Excel Discussion (Misc queries) | |||
Lookup values in a column and display them in order with no gaps | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions |