hi
i am trying to search over a range only as far as the first empty cell
eg row 274

= COUNTIF(BH2:BH134,AT2)
what i would like to do is use the value of the first empty cell
so the search would be BH2: "First empty cell",AT2




the formula below returns the row number of the last row with data in
it
=(MATCH("EMPTY",INDIRECT("BA1:BA"&COUNTIF(BA:BA,"< ")+1)&"EMPTY",
0)-1))

ie 250 ( this could change from worksheet to workheet)
so what i am lookig to do is

= COUNTIF(BH2:BH250,AT2) and BH250 to remain contant when i fill the
column with a the fomula

You forgot to mention the MATCH is an array formula
With the MATCH formula (giving 250) in say AT1
= COUNTIF(INDIRECT("BH2:BH"&$AT$1,AT2)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"kevcar40" wrote in message
ps.com...
hi
i am trying to search over a range only as far as the first empty cell
eg row 274

= COUNTIF(BH2:BH134,AT2)
what i would like to do is use the value of the first empty cell
so the search would be BH2: "First empty cell",AT2




the formula below returns the row number of the last row with data in
it
=(MATCH("EMPTY",INDIRECT("BA1:BA"&COUNTIF(BA:BA,"< ")+1)&"EMPTY",
0)-1))

ie 250 ( this could change from worksheet to workheet)
so what i am lookig to do is

= COUNTIF(BH2:BH250,AT2) and BH250 to remain contant when i fill the
column with a the fomula

On 12 Mar, 13:22, "Bernard Liengme"
wrote:
You forgot to mention the MATCH is an array formula
With the MATCH formula (giving 250) in say AT1
= COUNTIF(INDIRECT("BH2:BH"&$AT$1,AT2)
--
Bernard V Liengmewww.stfx.ca/people/bliengme
remove caps from email

"kevcar40" wrote in message

ps.com...



hi
i am trying to search over a range only as far as the first empty cell
eg row 274

= COUNTIF(BH2:BH134,AT2)
what i would like to do is use the value of the first empty cell
so the search would be BH2: "First empty cell",AT2

the formula below returns the row number of the last row with data in
it
=(MATCH("EMPTY",INDIRECT("BA1:BA"&COUNTIF(BA:BA,"< ")+1)&"EMPTY",
0)-1))

ie 250 ( this could change from worksheet to workheet)
so what i am lookig to do is

= COUNTIF(BH2:BH250,AT2) and BH250 to remain contant when i fill the
column with a the fomula- Hide quoted text -

- Show quoted text -

thank you
it works a treat