Forum Discussion
Count number of blank cells till last non empty cell in a column.
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.
- 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,
My suggestion is set the range for one time, and make this range expandable somehow so that if you add a new record, the range expands to include this new record.
The way to do that is to convert the normal range to Table and refer COUNTBLANK to a column in this table!
Please find the attached file to test this suggestion.
Thank you Haytham, but unfortunately I will not be able to use this exactly the way in which I want to use.
However, I thought of following but failed on below also, just see if you have solution to my below question if you would have encountered.
I tried capturing the last row number of the non empty cell with the below formula (considering maximum 6000 records that I will have at any time).
=SUMPRODUCT(MAX((A1:A6000<>"")*ROW(A1:A6000)))
Let us assume with above formula I get value as 390.
Now, with above formula I am getting the last non empty row number of a column.
No I want to pass this value from above formula into the Countblank function for the second half but only for the row number.
For example:
Countblank(A1:A390)
In above formula I want to pass 390 number as a result of SUMPRODUCT function used above.
I tried using the INDIRECT function for passing 390 value, but do not want to fix the column name because INDIRECT function requires column name to be fixed.
Thus not able to solve my issue.
Any idea?
- You can try taking advantage of Index function
Your dynamic range should be =$A$1:INDEX(A:A,SUMPRODUCT(MAX((A:A<>"")*ROW(A:A))))
