Conditional Sum Rank

[shawnandbarbie]

I have a set of data for which I want to Sum the number of shares held by my top 10 active shareholders. The number of shares held is in Column E and the text of either "Active" or "Passive" is in Column K. I tried this formula {=SUM(IF(IF(K4:K301="Active",RANK(E4:E301,E4:E301))<=10,E4:E301,0))} but it sums the shares held by the top 10 investors which are also "Active" rather than excluding the "Passive" Shareholders when determning which are the top 10. Can anyone help?

 

[barry houdini]

Hello shawnandbarbie, welcome to MrExcel, try this version

=SUM(LARGE(IF(K4:K301="Active",E4:E301),{1,2,3,4,5,6,7,8,9,10}))

confirmed with CTRL+SHIFT+ENTER

 

[shawnandbarbie]

Perfect! Thanks very much.