Change formula that removes blank cells to also exclude certain text from output list.

[Big.Moe]

In the attached file (starting @ G470), I'm using the following formula to create a 'summation' of daily sales without all the blank cells:

Please Login or Register  to view this content.

Everything works great except for, due to print-out formatting, I had to insert the column headers at each print page break, and hence the header text gets picked-up by the formula and gets included in the list. Is there a way to append this formula to exclude these headers?

[Sam Capricci]

cant you use the page setup >> sheet tab >> check the "Rows to repeat at top:" section instead?

[Big.Moe]

Can't use that due to office print-out customization demands.

[jason.b75]

Give this a go

=IFERROR(INDEX($G$40:$G$466,SMALL((IF(IFERROR(SIGN($G$40:$G$466),0),ROW($G$40:$G$466)-ROW($G$40)+1)),ROWS($G$470:$G470))),"")

edit:-

Just noticed that your original formula returned cells that contained 0, which this will not. Time to start again.

second edit:-

This one will find the 0 value non blank cells.

=IFERROR(INDEX($G$40:$G$466,SMALL((IF(IFERROR((LEN($G$40:$G$466)+$G$40:$G$466)>0,0),ROW($G$40:$G$466)-ROW($G$40)+1)),ROWS($G$470:$G470))),"")

I'll leave both formulas here so you can choose which will suit your need best.

[Big.Moe]

jason.b75

Let me play with that formula a bit, the zero thing may end up not being a factor.

[jason.b75]

Just edited my post again, first formula will not return 0 values, second one will. Both ignore blanks and text.

[Big.Moe]

jason.b75

So sorry, the zero thing IS indeed a factor in that sometimes items are comp'ed and hence have a zero price. Plus, I will be using the same formula in the other columns which will include negative numbers as well.

Is there a way to incorporate ISNUMBER perhaps?

edit:
Playing with 2nd formula....

[jason.b75]

Is there a way to incorporate ISNUMBER perhaps?

Sometimes it is so easy to overlook something because it is too obvious

=IFERROR(INDEX($G$40:$G$466,SMALL((IF(ISNUMBER($G$40:$G$466),ROW($G$40:$G$466)-ROW($G$40)+1)),ROWS($G$470:$G470))),"")

[Big.Moe]

jason.b75, you are a genius!

Although I got your 2nd formula to work with the negative numbers by changing this portion LEN($G$40:$G$466)+$G$40:$G$466)>0 to this LEN($G$40:$G$466)+$G$40:$G$466)<>0, your third formula is perfection.

Thanks so much for sticking with it & finding the ultimate solution! You sir are an Excel savior.

[jason.b75]

You're welcome!

Thanks for the feedback (and for suggesting the obvious thing that I had overlooked)