Array formula trouble

[amcmlt]

Hello, I am working on developing an array formula that will skip over cells that equal zero or blank. I have two types of products listed in eight lot categories. Lot one is S-Lot and Lot two is H-Lot. Each lot can have four categories. The trouble comes in here. The maximum number of lots is four and could be as low as one. The cells that are shown at zero or blank are being included in the array list as blank cells. I need those blank cells to be eliminated in the array list.

S-Lot 1 25,000
S-Lot 2 25,000
S-Lot 3
S-Lot 4
H-Lot 1 30,000
H-Lot 2 35,000
H-Lot 3
H-Lot 4

What is happening is S-Lot 3 and S-Lot 4 are being included in the formula and the list appears as...

25,000
25,000


30,000
35,000

Formula
{=IF($AM$21>=ROWS($AN21:AN21),INDEX($AJ:$AJ,SMALL(IF(LEN($AJ$21:$AJ$30)>=0,ROW($AJ$21:$AJ$30)),ROWS(AN$21:AN22))))}

Any help is greatly appreciated.

Thank you.

 

[steve the fish]

The length of a blank cell is equal to zero so quite rightly shows. Try changing it to >0 instead.

 

[amcmlt]

Thank you for such a quick response. When the change is made from >=0 to >0 the formula drops off S-Lot 1 and lists S-Lot 2 twice.


SORRY - I'm a little slow this morning. I made the correct in the formula back to the correct start cell and all is good. Again thank you!!