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.
Hello Mohammad -
I agree with you, your formula gives the desired result, but when I try to use this formula in the same column in which I have the data I am getting an error, maybe because I am not able to modify the formula as per my need.
I am attaching the excel sheet, were I have mentioned your formula in column other than A (in cell B2 highlighted in green color), but I want formula to work in the same data column that is in cell A2 highlighted in yellow color.
I tried using the formula with couple of modifications but failed, can you guide me, maybe I am missing on using it properly :(
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.
- Many Thanks Sergei for your help, it works great.
- Many Thanks Jamil for your help, it works great.
You may change IFNA on IFERROR
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<>"")))))
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.
Hello -
When I downloaded your file, I am not getting result, it shows error.
I have taken screenshot "1" when file is downloaded and opened, and "2" when I pasted the formula in the cell.
Am i doing something wrong at me end, or is it Excel version compatibility issue; I am using Excel 2010?
Thank You,
Saurabh Khanna.
Or
=IFNA(COUNTBLANK(A3:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A)))),0)
(regular formula)
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 -
I am able to get the desired results and formula also works if i Past it for any column.
However, I noticed that if there is no data in a specified range for that column, then it does not work properly, can there be a way to treat this situation of no data as an exception?
Attaching the reference sheet with an example scenario.
Thank You,
Saurabh.
=COUNTBLANK(A$4:INDEX($A4:$A$500000,SUMPRODUCT(MAX(($A4:$A$500000<>"")*(ROW($A4:$A$500000)-ROW($A$4)+1)))))
or
=COUNTBLANK(OFFSET(A$4,0,0,AGGREGATE(14,6,1/($A4:$A$500000<>"")*(ROW($A4:$A$500000)-ROW($A$4)+1),1)))
Hello Jamil -
Your suggestion worked for me to a great extent.
I was trying to post for last couple of days but was not able to post.
And I was using your listed suggestion for my work.
I have one small help again from you, attached is the excel sheet in which I am using your suggestion, however, I am able to use formula not in the same column in which count has to happen, I am able to use formula in cell other than column in which it is required.
For example, I have data in column A but formula I am able to use for Column A is in Column B.
Is it possible to use the formula in the same column in which the data is?
Thank you for your help.
Saurabh Khanna.
