Dynamic Range Ingoring Blank Cell with Formula

[Dokat]

Hi,

I have below dynamic range but i'd like formula to ignore blank cells with formulas. Does anyone have a solution for it?

"=OFFSET(Inno!$AU$6,0,0,COUNTA(Inno!$AU:$AU)-1,1)"

Thanks

 

[jayalaxmi]

Hi Dokat,

Try using this formula =OFFSET(Inno!$AU$6,0,0,COUNTIF(Inno!$AU:$AU,"<>"")-countif(Inno!$AU:$AU,""")) or try replacing countA with count formula.

Hope it helps!!!

Thanks
Jaya

 

[bosco_yip]

1] What is the type of your dynamic range? text or numeric?

2] Where are the blank cells? in-between or after the range ?

Regards
Bosco

 

[Dokat]

Hi Dokat,

Try using this formula =OFFSET(Inno!$AU$6,0,0,COUNTIF(Inno!$AU:$AU,"<>"")-countif(Inno!$AU:$AU,""")) or try replacing countA with count formula.

Hope it helps!!!

Thanks
Jaya

It worked. Thanks

 

[Peter Bartholomew]

Unless you range contains multi-cell array formulas, I would recommend using a Table. That makes the table body range or any column within it fully dynamic without counting or searching for the end (no volatile functions). If you actually need to know the number of records that is given by
= ROWS(tableName)

 

[Dokat]

1] What is the type of your dynamic range? text or numeric?

2] Where are the blank cells? in-between or after the range ?

Regards
Bosco

Previous solution worked . Thank you