Forum Discussion

Copper Contributor

Mar 18, 2018

Solved

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...

Formulas & Functions

Requests

Jamil
Mar 19, 2018
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.

Jamil

Bronze Contributor

Mar 18, 2018

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))))

Jamil

Bronze Contributor

Mar 18, 2018

If you have many formulas then I don’t recommend using INDIRECT function, as it will slowdown your workbook. INDIRECT Is a volatile function.