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?)...

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?)...

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?)...

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
__________________________

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

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

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