Sumif + Large = headache

turkey leftovers for first elegant solution ..

col A col B
-------------------
RR 450
SS 350
TT 250
TT 300
RR 450
RR 400
SS 450
TT 500


what i need is formula out in column C that will pick off the largest x
values in colunn B based on criteria A.

e.g. sumif (large("TT"s, 2) = 800

having trouble isolating the 'TTs' in separate array to put in Large
function - want to keep entire table in this order, as formula will be
copied down and range will be based on dates in another column

TIA

=MAX(IF($A$3:$A$10=A3,$B$3:$B$10)) array entered using CTRL+SHIFT+ENTER and
then copied down

or

=SUMPRODUCT(MAX(($A$3:$A$10=A3)*($B$3:$B$10))) entered normally and then
copied down

Cranberry sauce as well please. :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Hops" <kev8128@yahoo.com> wrote in message
news:1133042748.506268.244400@g14g2000cwa.googlegroups.com...
>
>
> turkey leftovers for first elegant solution ..
>
> col A col B
> -------------------
> RR 450
> SS 350
> TT 250
> TT 300
> RR 450
> RR 400
> SS 450
> TT 500
>
>
> what i need is formula out in column C that will pick off the largest x
> values in colunn B based on criteria A.
>
> e.g. sumif (large("TT"s, 2) = 800
>
> having trouble isolating the 'TTs' in separate array to put in Large
> function - want to keep entire table in this order, as formula will be
> copied down and range will be based on dates in another column
>
> TIA
>

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:%23Xezpct8FHA.2816@tk2msftngp13.phx.gbl...
> =MAX(IF($A$3:$A$10=A3,$B$3:$B$10)) array entered using CTRL+SHIFT+ENTER
> and then copied down
>
> or
>
> =SUMPRODUCT(MAX(($A$3:$A$10=A3)*($B$3:$B$10))) entered normally and then
> copied down


these will pick off the largest value - I need to pick off the largest
*five* values (orginal example below was largest two) and add together


> Cranberry sauce as well please. :-)


not quite yet.



> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------*------------------------------*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------*------------------------------*----------------
>
>
> "Hops" <kev8128@yahoo.com> wrote in message
> news:1133042748.506268.244400@g14g2000cwa.googlegroups.com...
>>
>>
>> turkey leftovers for first elegant solution ..
>>
>> col A col B
>> -------------------
>> RR 450
>> SS 350
>> TT 250
>> TT 300
>> RR 450
>> RR 400
>> SS 450
>> TT 500
>>
>>
>> what i need is formula out in column C that will pick off the largest x
>> values in colunn B based on criteria A.
>>
>> e.g. sumif (large("TT"s, 2) = 800
>>
>> having trouble isolating the 'TTs' in separate array to put in Large
>> function - want to keep entire table in this order, as formula will be
>> copied down and range will be based on dates in another column
>>
>> TIA
>>
>
>

Apologies, guess i didn't twig what you were actually after. Also not quite
sure on how you are laying this out, but assuming your data is in A2:A30 and
B2:B30 and that you want to put a formula in C2:C30, use

=SUMPRODUCT(LARGE(($A$2:$A$30=A2)*($B$2:$B$30),{1,2,3,4,5}))

or

=SUMPRODUCT(LARGE(($A$2:$A$30=A2)*($B$2:$B$30),ROW(INDIRECT("1:5"))))

Regards
Ken..............


"Hops" <kev8128nossssspam@yahoo.com> wrote in message
news:34KdnZHr6opKeRXenZ2dnUVZ_vudnZ2d@giganews.com...
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
> news:%23Xezpct8FHA.2816@tk2msftngp13.phx.gbl...
>> =MAX(IF($A$3:$A$10=A3,$B$3:$B$10)) array entered using CTRL+SHIFT+ENTER
>> and then copied down
>>
>> or
>>
>> =SUMPRODUCT(MAX(($A$3:$A$10=A3)*($B$3:$B$10))) entered normally and then
>> copied down
>
>
> these will pick off the largest value - I need to pick off the largest
> *five* values (orginal example below was largest two) and add together
>
>
>> Cranberry sauce as well please. :-)
>
>
> not quite yet.
>
>
>
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ------------------------------*------------------------------*----------------
>> It's easier to beg forgiveness than ask permission :-)
>> ------------------------------*------------------------------*----------------
>>
>>
>> "Hops" <kev8128@yahoo.com> wrote in message
>> news:1133042748.506268.244400@g14g2000cwa.googlegroups.com...
>>>
>>>
>>> turkey leftovers for first elegant solution ..
>>>
>>> col A col B
>>> -------------------
>>> RR 450
>>> SS 350
>>> TT 250
>>> TT 300
>>> RR 450
>>> RR 400
>>> SS 450
>>> TT 500
>>>
>>>
>>> what i need is formula out in column C that will pick off the largest x
>>> values in colunn B based on criteria A.
>>>
>>> e.g. sumif (large("TT"s, 2) = 800
>>>
>>> having trouble isolating the 'TTs' in separate array to put in Large
>>> function - want to keep entire table in this order, as formula will be
>>> copied down and range will be based on dates in another column
>>>
>>> TIA
>>>
>>
>>
>
>

that's it! thnx

http://members.tripod.com/screensave...g/wall3med.jpg





"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:ekqWkxt8FHA.1484@tk2msftngp13.phx.gbl...
> Apologies, guess i didn't twig what you were actually after. Also not
> quite sure on how you are laying this out, but assuming your data is in
> A2:A30 and B2:B30 and that you want to put a formula in C2:C30, use
>
> =SUMPRODUCT(LARGE(($A$2:$A$30=A2)*($B$2:$B$30),{1,2,3,4,5}))
>
> or
>
> =SUMPRODUCT(LARGE(($A$2:$A$30=A2)*($B$2:$B$30),ROW(INDIRECT("1:5"))))
>
> Regards
> Ken..............
>
>
> "Hops" <kev8128nossssspam@yahoo.com> wrote in message
> news:34KdnZHr6opKeRXenZ2dnUVZ_vudnZ2d@giganews.com...
>>
>> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
>> news:%23Xezpct8FHA.2816@tk2msftngp13.phx.gbl...
>>> =MAX(IF($A$3:$A$10=A3,$B$3:$B$10)) array entered using CTRL+SHIFT+ENTER
>>> and then copied down
>>>
>>> or
>>>
>>> =SUMPRODUCT(MAX(($A$3:$A$10=A3)*($B$3:$B$10))) entered normally and
>>> then copied down
>>
>>
>> these will pick off the largest value - I need to pick off the largest
>> *five* values (orginal example below was largest two) and add together
>>
>>
>>> Cranberry sauce as well please. :-)
>>
>>
>> not quite yet.
>>
>>
>>
>>> Regards
>>> Ken....................... Microsoft MVP - Excel
>>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>>
>>> ------------------------------*------------------------------*----------------
>>> It's easier to beg forgiveness than ask permission :-)
>>> ------------------------------*------------------------------*----------------
>>>
>>>
>>> "Hops" <kev8128@yahoo.com> wrote in message
>>> news:1133042748.506268.244400@g14g2000cwa.googlegroups.com...
>>>>
>>>>
>>>> turkey leftovers for first elegant solution ..
>>>>
>>>> col A col B
>>>> -------------------
>>>> RR 450
>>>> SS 350
>>>> TT 250
>>>> TT 300
>>>> RR 450
>>>> RR 400
>>>> SS 450
>>>> TT 500
>>>>
>>>>
>>>> what i need is formula out in column C that will pick off the largest x
>>>> values in colunn B based on criteria A.
>>>>
>>>> e.g. sumif (large("TT"s, 2) = 800
>>>>
>>>> having trouble isolating the 'TTs' in separate array to put in Large
>>>> function - want to keep entire table in this order, as formula will be
>>>> copied down and range will be based on dates in another column
>>>>
>>>> TIA
>>>>
>>>
>>>
>>
>>
>
>

ROTFLMAO

Like it!!!!!!

Cheers
Ken.................

"Hops" <kev8128nossssspam@yahoo.com> wrote in message
news:hu2dnR0CQoDOcBXeRVn-rA@giganews.com...
>
>
> that's it! thnx
>
> http://members.tripod.com/screensave...g/wall3med.jpg
>
>
>
>
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
> news:ekqWkxt8FHA.1484@tk2msftngp13.phx.gbl...
>> Apologies, guess i didn't twig what you were actually after. Also not
>> quite sure on how you are laying this out, but assuming your data is in
>> A2:A30 and B2:B30 and that you want to put a formula in C2:C30, use
>>
>> =SUMPRODUCT(LARGE(($A$2:$A$30=A2)*($B$2:$B$30),{1,2,3,4,5}))
>>
>> or
>>
>> =SUMPRODUCT(LARGE(($A$2:$A$30=A2)*($B$2:$B$30),ROW(INDIRECT("1:5"))))
>>
>> Regards
>> Ken..............
>>
>>
>> "Hops" <kev8128nossssspam@yahoo.com> wrote in message
>> news:34KdnZHr6opKeRXenZ2dnUVZ_vudnZ2d@giganews.com...
>>>
>>> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
>>> news:%23Xezpct8FHA.2816@tk2msftngp13.phx.gbl...
>>>> =MAX(IF($A$3:$A$10=A3,$B$3:$B$10)) array entered using
>>>> CTRL+SHIFT+ENTER and then copied down
>>>>
>>>> or
>>>>
>>>> =SUMPRODUCT(MAX(($A$3:$A$10=A3)*($B$3:$B$10))) entered normally and
>>>> then copied down
>>>
>>>
>>> these will pick off the largest value - I need to pick off the largest
>>> *five* values (orginal example below was largest two) and add together
>>>
>>>
>>>> Cranberry sauce as well please. :-)
>>>
>>>
>>> not quite yet.
>>>
>>>
>>>
>>>> Regards
>>>> Ken....................... Microsoft MVP - Excel
>>>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>>>
>>>> ------------------------------*------------------------------*----------------
>>>> It's easier to beg forgiveness than ask permission :-)
>>>> ------------------------------*------------------------------*----------------
>>>>
>>>>
>>>> "Hops" <kev8128@yahoo.com> wrote in message
>>>> news:1133042748.506268.244400@g14g2000cwa.googlegroups.com...
>>>>>
>>>>>
>>>>> turkey leftovers for first elegant solution ..
>>>>>
>>>>> col A col B
>>>>> -------------------
>>>>> RR 450
>>>>> SS 350
>>>>> TT 250
>>>>> TT 300
>>>>> RR 450
>>>>> RR 400
>>>>> SS 450
>>>>> TT 500
>>>>>
>>>>>
>>>>> what i need is formula out in column C that will pick off the largest
>>>>> x
>>>>> values in colunn B based on criteria A.
>>>>>
>>>>> e.g. sumif (large("TT"s, 2) = 800
>>>>>
>>>>> having trouble isolating the 'TTs' in separate array to put in Large
>>>>> function - want to keep entire table in this order, as formula will be
>>>>> copied down and range will be based on dates in another column
>>>>>
>>>>> TIA
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>