Show a shortened list of non 0 values

[slam]

Hi all!

I have 9 Cells with values in column F, which could be a 0, or > 0.

From those 9 cells, I want to create a separate list of only the cells > 0

So let's say my values were

22
0
34
22
0
9
0
0
4

I would want my formula list to only show:

22
34
22
9
4

I thought I had it with this formula in the 1st cell (note that some of my cells are merged which is why the cell references are not consecutive):

=IF($F$3>0,$F$3,IF($F$8>0,$F$8,IF($F$12>0,$F$12,IF($F$13>0,$F$13,IF($F$14>0,$F$14,IF($F$16>0,$F$16,IF($F$22>0,$F$22,IF($F$26>0,$F$26,IF($F$27>0,$F$27,"")))))))))

This formula in the 2nd cell

=IF($F$8>0,$F$8,IF($F$12>0,$F$12,IF($F$13>0,$F$13,IF($F$14>0,$F$14,IF($F$16>0,$F$16,IF($F$22>0,$F$22,IF($F$26>0,$F$26,IF($F$27>0,$F$27,""))))))))

... and so on down to this formula in the 9th cell:

=IF($F$27>0,$F$27,"")

However, I then discovered that this only works if the > 0 values are consecutive, and it wouldn't work on my example above.

Could someone be so kind as to assist with this?

This is for Excel 365

Thanks!

 

[RoryA]

Does =FILTER(F3:F27,F3:F27>0) work? (entered in one cell and clear all the cells below it first)

 

[slam]

Does =FILTER(F3:F27,F3:F27>0) work? (entered in one cell and clear all the cells below it first)

Amazing! I've never used that function before. So simple and works perfectly. Thank you so much!

 

[RoryA]

Glad we could help. FILTER is new to 365 (and 2021)