Skip empty cells when preforming a function

New Contributor

I only want to display in the table on the right the values which have values adjacent to their SKUs.

meaning that I don't want SKU 3, 5, 7, 8 and 10 (any SKU that doesn't have a quantity value beside it).

I want the table on the right to only include the SKUs which have quantity values and omit any row having a zero in "batch size" column.

any help?




4 Replies
best response confirmed by AhmedMedhat66 (New Contributor)



You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell E2 and filled across range E2:F10 in the example.

skip empty cells.JPGIf you work with Office 365 you can apply the FILTER function. 

the function works like magic, thanks alot


Practically the same

=IFERROR( INDEX( $A$2:$A$11, AGGREGATE(15,6, 1/($B$2:$B$11<>"")*(ROW($A$2:$A$11)-ROW($A$1)), ROW()-ROW($E$1)) ), "" )