Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum largest numbers based on condition
I did a quick search for something I know has been asked many times before -
sorry to repost... I need to sum the 5 largest numbers in column B where the year in column A is equal to 2006 Thanks very much in advance (bob?)... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum largest numbers based on condition
This seemed to work okay (array entered-you must hit Control+Shift+Enter
after typing/pasting it into the formula bar). Change ranges as needed =SUM(LARGE(IF(YEAR(A1:A11)=2006,B1:B11,""), ROW(INDIRECT("1:"&MIN(5,SUM(--(YEAR(A1:A11)=2006))))))) If you will always have at least 5 values that meet the criteria, you can change ROW(INDIRECT("1:"&MIN(5,SUM(--(YEAR(A1:A11)=2006))))) to ROW(INDIRECT("1:5")) "Daniel Bonallack" wrote: I did a quick search for something I know has been asked many times before - sorry to repost... I need to sum the 5 largest numbers in column B where the year in column A is equal to 2006 Thanks very much in advance (bob?)... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum largest numbers based on condition
superb, thanks!
"JMB" wrote: This seemed to work okay (array entered-you must hit Control+Shift+Enter after typing/pasting it into the formula bar). Change ranges as needed =SUM(LARGE(IF(YEAR(A1:A11)=2006,B1:B11,""), ROW(INDIRECT("1:"&MIN(5,SUM(--(YEAR(A1:A11)=2006))))))) If you will always have at least 5 values that meet the criteria, you can change ROW(INDIRECT("1:"&MIN(5,SUM(--(YEAR(A1:A11)=2006))))) to ROW(INDIRECT("1:5")) "Daniel Bonallack" wrote: I did a quick search for something I know has been asked many times before - sorry to repost... I need to sum the 5 largest numbers in column B where the year in column A is equal to 2006 Thanks very much in advance (bob?)... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum largest numbers based on condition
On Wed, 2 Aug 2006 16:33:02 -0700, Daniel Bonallack
wrote: I did a quick search for something I know has been asked many times before - sorry to repost... I need to sum the 5 largest numbers in column B where the year in column A is equal to 2006 Thanks very much in advance (bob?)... =SUM(LARGE((B1:B10)*(A1:A10=2006),{5,4,3,2,1})) is an array formula and should be committed with CtrlShiftEnter Change ranges to suit. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum largest numbers based on condition
Richard Buttrey wrote: On Wed, 2 Aug 2006 16:33:02 -0700, Daniel Bonallack wrote: I did a quick search for something I know has been asked many times before - sorry to repost... I need to sum the 5 largest numbers in column B where the year in column A is equal to 2006 Thanks very much in advance (bob?)... =SUM(LARGE((B1:B10)*(A1:A10=2006),{5,4,3,2,1})) is an array formula and should be committed with CtrlShiftEnter Change ranges to suit. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ Hi, I have used a similar formula to sum the largest numbers with a condition which works fine as below: =SUM(IF((LARGE($B$5:$B$37*($G$5:$G$37="Final"),{5, 4,3,2,1})=$B$5:$B$37),$E$5:$E$37,0)) Column B are dates Column G is text Column E is numbers however this formula does not work when i replace {5,4,3,2,1} with ROW(INDIRECT("1:5")) ... any ideas or a better way to do this? cheers- Ben |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum largest numbers based on condition
Hi!
Maybe this: =SUMPRODUCT(--(B5:B37=LARGE(B5:B37,5)),--(G5:G37="final"),E5:E37) Note that using your current formula, if there are duplicate dates that fall within the largest 5 dates you'll get incorrect results. Try it on this data: B5:B9 = 8/25/2006 G5:G9 = final E5:E9 = 1 Result = 35 when it should be 5. Biff wrote in message oups.com... Richard Buttrey wrote: On Wed, 2 Aug 2006 16:33:02 -0700, Daniel Bonallack wrote: I did a quick search for something I know has been asked many times before - sorry to repost... I need to sum the 5 largest numbers in column B where the year in column A is equal to 2006 Thanks very much in advance (bob?)... =SUM(LARGE((B1:B10)*(A1:A10=2006),{5,4,3,2,1})) is an array formula and should be committed with CtrlShiftEnter Change ranges to suit. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ Hi, I have used a similar formula to sum the largest numbers with a condition which works fine as below: =SUM(IF((LARGE($B$5:$B$37*($G$5:$G$37="Final"),{5, 4,3,2,1})=$B$5:$B$37),$E$5:$E$37,0)) Column B are dates Column G is text Column E is numbers however this formula does not work when i replace {5,4,3,2,1} with ROW(INDIRECT("1:5")) ... any ideas or a better way to do this? cheers- Ben |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum largest numbers based on condition
I need to get my eyes checked!
Result = 35 when it should be 5. Result is 25 when it should be 5. Biff "Biff" wrote in message ... Hi! Maybe this: =SUMPRODUCT(--(B5:B37=LARGE(B5:B37,5)),--(G5:G37="final"),E5:E37) Note that using your current formula, if there are duplicate dates that fall within the largest 5 dates you'll get incorrect results. Try it on this data: B5:B9 = 8/25/2006 G5:G9 = final E5:E9 = 1 Result = 35 when it should be 5. Biff wrote in message oups.com... Richard Buttrey wrote: On Wed, 2 Aug 2006 16:33:02 -0700, Daniel Bonallack wrote: I did a quick search for something I know has been asked many times before - sorry to repost... I need to sum the 5 largest numbers in column B where the year in column A is equal to 2006 Thanks very much in advance (bob?)... =SUM(LARGE((B1:B10)*(A1:A10=2006),{5,4,3,2,1})) is an array formula and should be committed with CtrlShiftEnter Change ranges to suit. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ Hi, I have used a similar formula to sum the largest numbers with a condition which works fine as below: =SUM(IF((LARGE($B$5:$B$37*($G$5:$G$37="Final"),{5, 4,3,2,1})=$B$5:$B$37),$E$5:$E$37,0)) Column B are dates Column G is text Column E is numbers however this formula does not work when i replace {5,4,3,2,1} with ROW(INDIRECT("1:5")) ... any ideas or a better way to do this? cheers- Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill a cell based on a condition being met | Excel Worksheet Functions | |||
Defining a Text field based on its condition | Excel Discussion (Misc queries) | |||
Sum numbers based on the contents of another cell | Excel Worksheet Functions | |||
Calculation based on a condition | Excel Worksheet Functions | |||
make a cell empty based on condition | Charts and Charting in Excel |