Count Blank Cells that are within the Dynamic Range

[lyq0077]

I'm trying to include the empty cells that are in my dynamic range. For example,

A (A1)
Blank (A2)
B (A3)
Blank (A4)
C (A5)
Blank (A6)


I use the formula =OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A),) which looks at the entire column and returns 3 and so the dynamic range only runs to A3, how can I rewrite the formula so it'll include all the way to A5 (Not A6 because A5 is the last cell with text/number in it)

 

[bosco_yip]

Try,

1] for text in it only
=OFFSET(Sheet1!A1,,,MATCH("zzzz",Sheet1!A:A))

2] for number in it only
=OFFSET(Sheet1!A1,,,MATCH(9.99E+307,Sheet1!A:A))

3] for text/number in it
=OFFSET(Sheet1!A1,,,INDEX(MATCH(2,1/(Sheet1!A:A<>"")),))

 

[Puertorekinsam]

This will return the row number of the last cell in the range
=MATCH(REPT("z",255),A:A)

 

[lyq0077]

Yes! I think it worked. What does the "zzzz" represent?

 

[GDH]

Giving the approximate version of MATCH a very large number to look for will always return the last cell in the range.
Because you are using an approximate version of MATCH the zzz is basically the "highest" value that match can return - but with text.

 

[lyq0077]

=MATCH(REPT("z",255),A:A) doesn't work...