=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)))
49. Sum Every Nth Row
If your numbers are in range A1:A100, use below formula
=SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0))
Above formula is for every 2nd row. Replace 2 with N. Hence, for every 5th row -
=SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,5)=0))
This is a generic formula and will work for any range. If you range is B7:B50, your formula
would become
=SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
50. We have AVERAGEIF. What about MEDIANIF and MODEIF?
Excel doesn't provide MEDIANIF and MODEIF. You will have to use Array formulas to
achieve these functionality. Let's assume that our data is like below –
To calculate MEDIANIF and MODEIF, enter below formulas i.e. not by pressing ENTER after
entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets
around the formula which you can see in Formula Bar. If you edit again, you will have to do
CTRL+SHIFT+ENTER again. Don't put { } manually.
