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
 
Ad

Advertisements

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

Cranberry sauce as well please. :)


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

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top