Formula Help: Adding smallest numbers, excluding zeros, unless the range is mostly or all zeros

Question

Hello, I am trying to create a formula that adds the three smallest values, of five, in a column, excluding zeros; unless there are more zeros than other numbers, then zeros need to be part of the sum. 

My current formula: 

=SUM(SMALL(IF(F8:F50<>0,F8:F50),{1,2,3}))

Current with mostly zeros

#NUM!
66
0
0
0
0

Current with at least three numbers

207
65
70
72
0
0

Thank you!

Answer 1

Hi,

You can nest another IF inside your IF to create another criteria such as >=-50. Then use SUMIF for your error value. Your formula would look something like this:

=IFERROR(SUM(SMALL(IF(F8:F50>=-50,IF(F8:F50<>0,F8:F50)),{1,2,3})),SUMIF(F8:F50,">=-50"))

Answer 2

Hi beeohbee, thank you for reaching out!

My name is Rolyn, an Independent Advisor and a Microsoft Excel User like you. I would love to help you out with your concern.

You can try using IFERROR function.

=IFERROR(SUM(SMALL(IF(F8:F50<>0,F8:F50),{1,2,3})),SUM(F8:F50))

Let me know if that worked out well for you. Feel free to ask me if you need further assistance.

Thanks,

Rolyn

Answer 3

Yes, you're correct. My apologies, I forgot to shift+control+enter. Thanks again for your help with this. I do have one more question slightly related. 

If I wanted to get the sum of four cells, but if any of them have a value in a range of -50 to -210, it sets the value to zero or ignores that value for the total. 

E.G.,

-9,-14.-210,-210 = -23

-9,-14,-105,-210 = -23

-9,-14,-9,-210 = -32

Could I use a modified version of your formula?  If so, what would need to be altered?

Answer 4

Hi beeohbee,

The IFERROR formula should still work. From your initial formula, it will always return a #NUM! if there are less than 3 cells with value that is not 0. So IFERROR will take effect whenever your range has only 2 or less values not zero, or if there are only zeros. Once there are three or more values that are not zero, it will now only add the smallest three values. You can try to input the values from your examples using the formula I gave you and see if they return the same. I tried recreating it myself, and it works pretty fine.

Thank you,

Rolyn

Answer 5

Thanks @Rolyn! this is great if the data were static. But, the range F8:F12 will be updated dynamically, and would need to account for all five cells to eventually have a value higher than zero. Until that point, when all five cells are filled, is there a way to add the zeros until at least three cells have a value? 

E.G.,

68, 72, 70, 72, 67 = 205

68, 0, 70, 72, 0 = 210

68, 0, 0, 72, 0 = 140

68, 0, 0, 0, 0 = 68

0, 0, 0, 0, 0, = 0

Thank you again.