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

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

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

On Wed, 2 Aug 2006 16:33:02 -0700, Daniel Bonallack
<[email protected]> 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 Ctrl>>Shift>>Enter

Change ranges to suit.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Richard Buttrey wrote:
> On Wed, 2 Aug 2006 16:33:02 -0700, Daniel Bonallack
> <[email protected]> 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 Ctrl>>Shift>>Enter
>
> 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

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

<[email protected]> wrote in message
news:[email protected]...
>
> Richard Buttrey wrote:
>> On Wed, 2 Aug 2006 16:33:02 -0700, Daniel Bonallack
>> <[email protected]> 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 Ctrl>>Shift>>Enter
>>
>> 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
>

I need to get my eyes checked!

>Result = 35 when it should be 5.

Result is 25 when it should be 5.

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> <[email protected]> wrote in message
> news:[email protected]...
>>
>> Richard Buttrey wrote:
>>> On Wed, 2 Aug 2006 16:33:02 -0700, Daniel Bonallack
>>> <[email protected]> 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 Ctrl>>Shift>>Enter
>>>
>>> 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
>>
>
>