Excel limit number of item in sum

[Manda855]

Is it possible to limit the number of items in a sum to the first 10 that are over a certain value.

For example if there are 50 cells with values ranging from 0 to 40, can I include a formula to only sum up the first 10 cells that contain a value over 20?

[#2 · Accepted solution]

@Manda855 

=SUM(SMALL(IF(A1:E10>20,A1:E10),{1,2,3,4,5,6,7,8,9,10}))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

 

[#3]

@OliverScheurich 

Fantastic works perfectly :)