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

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

Formula:

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?

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

jason.b75

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

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

Originally Posted by Big.Moe

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))),"")

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.

You're welcome!

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