Sumif + Large = headache

H

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
 
K

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 :)
------------------------------­------------------------------­----------------
 
K

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