adding 4 smallest numbers greater than zero

  • Thread starter Thread starter Tangyteacher
  • Start date Start date
T

Tangyteacher

I am a high school golf coach making a spreadsheet for my golfers stats using
excell 2007. I need to be able to add the 4 lowest values in a data table
that are greater than 0
Ex.
75,76,77,78,79,0,0 are the values. I need a formula that will automaticly
ignore the zero's and add the 4 smallest values. In this case it would be
75,76,77,78.

Any help would be appreciated!
Thanks!
 
Try this array formula**.

Assuming there are at least 4 numbers >0.

=SUM(SMALL(IF(A1:G1>0,A1:G1),{1,2,3,4}))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Might be able to shorten this somehow, but this array** formula works:

=SUM(SMALL(IF(A2:A10>0,A2:A10),1),SMALL(IF(A2:A10>0,A2:A10),2),SMALL(IF(A2:A10>0,A2:A10),3),SMALL(IF(A2:A10>0,A2:A10),4))

**Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.
 
Thanks Luke M... I really appreciate your help!

Luke M said:
Might be able to shorten this somehow, but this array** formula works:

=SUM(SMALL(IF(A2:A10>0,A2:A10),1),SMALL(IF(A2:A10>0,A2:A10),2),SMALL(IF(A2:A10>0,A2:A10),3),SMALL(IF(A2:A10>0,A2:A10),4))

**Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*
Click to expand...
 
Back
Top