Sum bottom n numbers greater than or equal to k

[raikks]

Hi. Can you help me with a formula that sums bottom 4 numbers that are greater than or equal to 2?
Ex - if i have the following numbers in column A (A1:A9), sum of bottom 4 numbers should be 8.66 (2.05+2.07+2.17+2.37):

2.17
1.60
3.90
2.37
1.28
4.80
3.30
2.05
2.07

Thanks in advance.

 

[Fluff]

How about

=SUM(AGGREGATE(15,6,A1:A9/(A1:A9>=2),{1,2,3,4}))

may need array entry.

 

[raikks]

Great, works fine - thanks a lot!
How did you pick 15 and 6?

 

[Fluff]

15 tells the function to act like the small function & the 6 tells it to ignore errors.

AGGREGATE Function

The Excel AGGREGATE function returns a aggregate calculation like AVERAGE, COUNT, MAX, etc., optionally ignoring hidden rows and errors. A total of 19 operations are available, specified by function number in the first argument (see table for options).

exceljet.net

 

[raikks]

The beauty of Excel formulas - never ceases to amaze me.
Thanks again for your support and for the link - cheers!

 

[Fluff]

You're welcome & thanks for the feedback.