# 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

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

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

H

#### Hops

Ken Wright said:
=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

not quite yet.

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

H

K

#### Ken Wright

ROTFLMAO

Like it!!!!!!

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