find first non blank cell in a row

[mdmarsh]

I'm back again. Hopefully, I won't be bothering you all much after this.

I need to know the value (which is a date) of the first non-blank cell in a range in a row. I have found some array formulas, but they either return it for the whole row (which I don't want), or they return text (which I don't want), or they don't work at all.

The range is: B9:FL9. Some of the cells in this range are blank, some are "0", and some contain dates. I need to know the most recent (furthest left) cell that contains a date and what that date is. Thanks!

 

[rpsundar]

Thanks Snasui, It works well.

 

[bradfoley]

Thanks Snasui, It works well.

I have a similar question, however not only am I looking for the furthest left cell, I need it to sum it and the three cells to the right. Any ideas on how to do that. Imagine the columns are quarters, and I want to sum the first quarter with results with the following three quarters.