Count If with formula or vba?

[omairhe]

peace.,

in range A10:A20.

If empty cell present then take the last row number in range which is A20 and subtract with the row number where the empty cell is present. Output the resulting value in cell E1.

Note: If more than 1 empty cell in range A10:A20 then apply the formula to the first empty cell from the top.

 

[Marcelo Branco]

Maybe

=$A$20-IF(COUNTIF($A$10:$A$20,"="),INDEX(ROW($A$10:$A$20),MATCH(TRUE,INDEX($A$10:$A$20="",0),0)),0)

M.

 

[omairhe]

Maybe

=$A$20-IF(COUNTIF($A$10:$A$20,"="),INDEX(ROW($A$10:$A$20),MATCH(TRUE,INDEX($A$10:$A$20="",0),0)),0)

M.

Not exactly what I was expecting but this will work for me if column A is auto numbered from row 10 till last data..

Thxxx

 

[Marcelo Branco]

You are welcome.

BTW, what exactly were you expecting?

M.

 

[omairhe]

The column A has alphabet data and numeric data was to be collected from row number if there is a formula for such.. =row maybe? . so I changed the destination formula so that column A carry the same numbering like the row has on A10 to A20.

 

[Marcelo Branco]

The column A has alphabet data and numeric data was to be collected from row number if there is a formula for such.. =row maybe? . so I changed the destination formula so that column A carry the same numbering like the row has on A10 to A20.

This is ok?

=ROW($A$20)-IF(COUNTIF($A$10:$A$20,"="),INDEX(ROW($A$10:$A$20),MATCH(TRUE,INDEX($A$10:$A$20="",0),0)),0)

 

[omairhe]

=ROW($A$20)-IF(COUNTIF($A$10:$A$20,"="),INDEX(ROW($A$10:$A$20),MATCH(TRUE,INDEX($A$10:$A$20="",0),0)),0)

yr code this time is perfectly working.
thank you.

 

[Marcelo Branco]

You are welcome and thanks for the feedback

M.