Sumif + Large = headache

[Hops]

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]

=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]

=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.

 

[Ken Wright]

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]

that's it! thnx

http://members.tripod.com/screensaver9/thanksgiving/wall3med.jpg

 

[Ken Wright]

ROTFLMAO

Like it!!!!!!

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