How to count the number of empty cells above entry...

joezeppy said:

Hi,

I'd like to be able to count the number of empty cells above the cell with the formula in it until a full cell is reached, and then just return that number.

Seems easy enough but I can't think of what function to use.

Any help appreciated.

Click to expand...

Let column A house the entries and the formula of interest.

If entries above the formula cell are numeric...

=ROW()-MATCH(9.99999999999999E+307,$A$1:OFFSET(INDEX(A:A,ROW()),-1,0))-1

which just needs enter.

If entries above the formula cell can be anything...

=ROW()-MATCH(9.99999999999999E+307,1/(1-ISBLANK($A$1:OFFSET(INDEX(A:A,ROW()),-1,0))))-1

which needs be confirmed with control+shift+enter.

Note. I didn't seek to simplify the formulas or look for simpler alternatives if any.

It Worked!

Dear Sir,

It worked.

I apologize for (I guess) a bit of cross posting. Didn't mean to do that.

I just ran across that other guys post after I did mine and the problem sounded similar.

Your solution worked great. I had to tweak it a bit in a very simple way (took out the -1 as the structure of my spread didn't need that).

I'm very impressed.

Now I'm going to have to really try to read that to see how you did it. The only way I ever understand formulas and functions is to say them out loud. LOL.

Thank you very much.

Best Regards,

Joe