SOLVED

# Excel limit number of item in sum

Copper Contributor

# Excel limit number of item in sum

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 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Excel limit number of item in sum

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

# Re: Excel limit number of item in sum

Fantastic works perfectly :)

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Excel limit number of item in sum

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