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.
Thank you Haytham, I had tried below formula earlier, and that is the issue which I had mentioned in my previous usage of INDIRECT function that column names are fixed and no longer remains dynamic.
Like in below example column A that is "A1:A" is fixed in this formula under the INDIRECT function. Although this formula works perfectly Ok for column A, but if I want to paste this formula for many other columns then I have to manually change this input under the quotes for every column and that is where I have an issue with this formula, it no longer remains dynamic and does not change column reference when pasting in other columns, if you can guide me on how to resolve this issue, then it perfectly solves my issue :) and I will be very happy to learn this :)
=COUNTBLANK(INDIRECT("A1:A"&SUMPRODUCT(MAX((A1:A6000<>"")*ROW(A1:A6000)))))
Hi Saurabh,
My friends in previous replies give you some supposed solutions.
Please test their solutions.
Each formula gives you the same result, except if the whole column is empty!
In this case, SergeiBaklan's formula returns a #REF! error, but Jamil's formula counts all cells in the column which are all blanks.
Haytham Amairah, thank you. When with wrap
=IFERROR(COUNTBLANK(OFFSET(B1,0,0,AGGREGATE(14,6,(B:B<>"")*ROW(B:B),1))),0)
