Formula Help - Sum 2 lowest values in a list

  • Thread starter Thread starter dlvgolf
  • Start date Start date
D

dlvgolf

I've got a list of 20 numbers. I want to sum the lowest 5 numbers i
that list. The "min" command lets me identify the lowest number, but
don't know how to sum the lowest 5 numbers. Any help would b
appreciated
 
next to the column with the values use the rank function

so if your values where in column a.. b1 would be:
=rank(a1,$a$1:$a$20,1)

Then at the bottom of the list have:
=sumif($b$1:$b$20,1,$a$1:$a$20) + sumif($b$1:$b$20,2,$a$1:$a$20)
sumif($b$1:$b$20,3,$a$1:$a$20) + sumif($b$1:$b$20,4,$a$1:$a$20)
sumif($b$1:$b$20,5,$a$1:$a$20)

Maybe there is a better way, but this will wor
 
Check out the 'SMALL' Worksheet Function

=SUM(SMALL(A1:A20,{1,2,3,4,5}))

This will do the lowest 5. If you want to do say, 8, you would use

=SUM(SMALL(A1:A20,{1,2,3,4,5,6,7,8}))

If you want to add just the 2nd and 4th lowest number

=SUM(SMALL(A1:A20,{2,4}))

All of these formulas assume your numbers are in A1:A20
Also Note the 'Curly' Brackets enclosing the {1,2,3 etc}

John

PS:The opposite is LARGE for the Largest 5 numbers

=SUM(LARGE(A1:A10,{1,2,3,4,5})
 
Back
Top