using sumif and rank formulas together...

[MCCCLXXXV]

Hello all,

I have a list of "weights" in column H. They can all be ranked from 1 to 30 (none are exactly the same). I want to write 1 formula that sums the top 10 (biggest) weights without having to make another column for "weight rank". I tried the following as an array formula, but it didn't work....

=SUMIF($H$3:$H$32,RANK($H$3:$H$32,$H$3:$H$32)&">=10")

Any ideas?

Thanks

[DonkeyOte]

Maybe

=SUM(LARGE(H3:H32,{1,2,3,4,5,6,7,8,9,10}))

or

=SUM(LARGE(H3:H32,ROW(INDIRECT("H1:H"&MIN(10,COUNT(H3:H32))))))
committed with CTRL + SHIFT + ENTER

The latter is Volatile and CSE array but will adapt should you have less than 10 numbers (assumes min of 1)

EDIT...

Another alternative would be to use SUMIF in conjuction with RANK & COUNT, eg:

=SUMIF(H3:H32,">="&LARGE(H3:H32,MIN(10,COUNT(H3:H32))))

This would be better than the CSE IMO..

[MCCCLXXXV]

awesome that last formula is perfect. thanks