CODEWITHSUNDEEP
excelchartsvba
priyanka -
priyanka -

Reputation: 21

Dynamic range in excel: How to remove Counta counting blank cells(made blank by ifferror formulas)

I have to plot a data in chart which I have made dynamic by using a named range using offset, counta(for required rows) in the chart area.

The problem with using counta is it counts the rows which has blank cells(made blank by iferror formula),which I do not want.Moreover I cannot remove those formulas 'coz when the data is there that has to be dynamically present in the chart.

OFFSET('ABC'!$T$2,1,0,COUNTA('ABC'!$T2:$T1000),4)

Here; let us say the values are there for 10 rows;but the formulas are till 15 so how do I stop counta from counting the 5 rows(which have been made blank by iferror).

Upvotes: 1

Views: 7129

Answers (1)

daZza
daZza

Reputation: 1689

To remove the blank cells from the count you can use the COUNTBLANK function of Excel.

Simply subtract the value of COUNTBLANK from the result of COUNTA

Example:

=COUNTA(yourRange)-COUNTBLANK(yourRange)

Upvotes: 1

Related Questions