Forum Discussion
Count number of blank cells till last non empty cell in a column.
- It does not work because of circular reference problem.
My formula range starts from A1 while you also want to use inside the same range.
To make this work you need to redefjne the starting row of your column A in the formula for example if your start of data starts from A4 then the modified formula should be
=COUNTBLANK(A$4:INDEX(A:A,SUMPRODUCT(MAX((A:A<>"")*ROW(A:A)))))
This should work.
Hi Saurabh,
this is the formula to put in A2 and drag to the right .
Please note that it is an array formula, so it must be entered with holding Control+Shift+Enter.
=IFNA(COUNTBLANK(A3:INDEX(A3:A1048576,MATCH(2,1/(A3:A1048576<>"")))),0)
see attached workbook
Hello -
When I downloaded your file, I am not getting result, it shows error.
I have taken screenshot "A" when file is downloaded and opened, and "B" when I pasted the formula in the cell(by Ctrl+Shift+Enter).
Am i doing something wrong at me end, or is it Excel version compatibility issue; I am using Excel 2010?
Thank You,
Saurabh Khanna.
- Many Thanks Jamil for your help, it works great.
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<>"")))))