Countif using last row with data in Excel?

Question

Without needing VBA, is there a way for me to say countif(column X, "") without counting all the empty cells that occur past the last row with data? The sheet is dynamic so I can't set it to a static range.

Answer 1

Like I mentioned in my comment, you can used Named Range else MATCH(REPT("z",255),A:A) will give you the row number of last cell which has data. So you can use it in CountIf like this

=COUNTIF(INDIRECT("A1:A" & MATCH(REPT("z",255),A:A)), "")

If you last row contains a numeric data then you this =MATCH(9.9E+307,A:A,1)

Answer 2

Siddharth Rout's answer is a good one and should work. If you happen to have less knowledge about the contents of the last row, you may want to try something like this:

=COUNTIF(INDIRECT("A1:A"&INDEX(MAX((A:A<>"")*(ROW(A:A))),1)),"")

Answer 3

=COUNTBLANK(COLX) - COUNTBLANK(COLY)

where COLY is a column that must have a value for every valid row (say, ID value)