Count number of blank cells till last non empty cell in a column.

[Saurabh Khanna]

Hello -

I want to count number of blank cells till last non empty cell in a column but could not figure out how to do this, can you please help?

When I use Countblank formula, it takes an argument as range which should be known in the beginning itself which is a problem because then it becomes manual work to mention the range everytime as my column data (number of rows) can change many times, and different columns can  have different number of rows.

Is there a way to mention end limit of the range as row number of last non empty cell in that column?

Thank you in advance,

Saurabh Khanna.

[#2]

it is because you are probably using Excel 2010 which does not have IFNA function.

SO i replaced is with IF ISNA 

 like this 

it is again with Control shift enter.  see attached

=IF(ISNA(COUNTBLANK(A3:INDEX(A3:A1048576,MATCH(2,1/(A3:A1048576<>""))))),0,COUNTBLANK(A3:INDEX(A3:A1048576,MATCH(2,1/(A3:A1048576<>"")))))

[#3]

You may change IFNA on IFERROR

[#4]

Many Thanks Jamil for your help, it works great.

[#5]

Many Thanks Sergei for your help, it works great.