SMALL function not counting blanks as zero

[Suefenton]

I want to find the two smallest numbers out of 12 cells and if there is a blank I want it to count it as a zero.

Eg: 25 20 22 25 .. 20 25 25 18 19 18 22 (where .. Is a blank but I must not put a zero in)

I want to pick the two lowest numbers as 0 and 18 in two separate columns.

I have one formula as =SMALL(A1:A12,1). It picks 18. I want 0.
i have other formula as =SMALL(A1:A12,2). It picks 18, correct

 

[JoeMo]

Try this array formula - confirmed with shift+ctrl+enter, not just enter.

=SMALL(IF(ISBLANK(A1:A12),0,A1:A12),1)

 

[mikerickson]

Try

=SMALL(A1:A12+0, 2)

entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[Suefenton]

Try this array formula - confirmed with shift+ctrl+enter, not just enter.

=SMALL(IF(ISBLANK(A1:A12),0,A1:A12),1)

This did it for me. Thanks so much. I had tried exactly that but didn't know about the shift Ctrl enter.