Suppose you have numbers in range A1:A100 and you want to sum up bottom N values
=SUMPRODUCT(SMALL($A$1:$A$100,ROW(1:10)))
In case, you want to ignore 0 values (and blanks)
=SUMPRODUCT(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)))
Both the above formulas will function only if there are at least N values as per ROW(1:N). Hence, for above formulas, it would work only if there are at least 10 numbers in A1 to A100.
To overcome this limitation –
Enter the below formulas as Array Formula
=SUM(IFERROR(SMALL($A$1:$A$100,ROW(1:10)),0))
=SUM(IFERROR(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)),0))
Non Array Versions of above formulas (For Excel 2010 and above)
=SUMPRODUCT(AGGREGATE(15,6,$A$1:$A$100,ROW(1:10)))
=SUMPRODUCT(AGGREGATE(15,6,$A$1:$A$100/($A$1:$A$100<>0),ROW(1:10)))