Count function to exclude a circular reference

Hi all,

I need a function to count the entire column, but need to exclude several rows.

The column will be dynamic, i.e. any number of rows may be added.
The first three rows contain headings and other data. the 4th row needs to have a function. For example: =CountA(B:B)

This function however produces a circular warning, understandably.

Is there a function that will count the entire column, but omit the cells. I know I could count 10000000, but that seems clumsy. Is there a better way?

Cheers

I'd say no, but maybe somebody will prove me wrong. You can't define a matrix from which to exclude cell 4, without first INCLUDING cell 4.

HI,

if your looking for one column then go for Name manager select the range and give it name and use the same in CountA function then it will not look odd and also you can make any change in the range with out disturbing the formula cell

Punnam

Originally Posted by Punnam

HI,

if your looking for one column then go for Name manager select the range and give it name and use the same in CountA function then it will not look odd and also you can make any change in the range with out disturbing the formula cell

Punnam

Hi Punnam, I thought of this, too, but it didn't work. You can't use the named range in a cell that's PART of that range without running into the same Circular Reference issue.