Column CX contains entries that either begin with a "B" or "O". I need to
count the number of cells whose contents begin with "B", and count the number
of cells whose contents begin with "O".
I wrote the following formula, and even saved it as an array formula, but I
keep getting a circular reference error:

=COUNTIF(CX:CX,LEFT(CX:CX,1)="B")

Any help would be greatly appreciated. Thanks.
Bob

Hi!

To solve the circular reference error, don't put the formula in column CX.

Try this:

=SUMPRODUCT(--(LEFT(CX1:CX100)="B"))
=SUMPRODUCT(--(LEFT(CX1:CX100)="O"))

You can't use entire columns as range arguments with Sumproduct (unless
you're using Excel 2007 beta).

Biff

"Bob" wrote in message
...
Column CX contains entries that either begin with a "B" or "O". I need to
count the number of cells whose contents begin with "B", and count the
number
of cells whose contents begin with "O".
I wrote the following formula, and even saved it as an array formula, but
I
keep getting a circular reference error:

=COUNTIF(CX:CX,LEFT(CX:CX,1)="B")

Any help would be greatly appreciated. Thanks.
Bob

Try:

=COUNTIF(CX:CX,"B*")

=COUNTIF(CX:CX,"O*")

"Bob" wrote:

Column CX contains entries that either begin with a "B" or "O". I need to
count the number of cells whose contents begin with "B", and count the number
of cells whose contents begin with "O".
I wrote the following formula, and even saved it as an array formula, but I
keep getting a circular reference error:

=COUNTIF(CX:CX,LEFT(CX:CX,1)="B")

Any help would be greatly appreciated. Thanks.
Bob

Thanks! I didn't realize that you could put wildcards within the quotes.
Thanks again.

"Toppers" wrote:

Try:

=COUNTIF(CX:CX,"B*")

=COUNTIF(CX:CX,"O*")

"Bob" wrote:

Column CX contains entries that either begin with a "B" or "O". I need to
count the number of cells whose contents begin with "B", and count the number
of cells whose contents begin with "O".
I wrote the following formula, and even saved it as an array formula, but I
keep getting a circular reference error:

=COUNTIF(CX:CX,LEFT(CX:CX,1)="B")

Any help would be greatly appreciated. Thanks.
Bob

Biff,
Thanks for the suggestion. Unfortunately, I need to put the formula in
column CX (for a variety of reasons).
It appears that the solution offered by Toppers (see the post after yours)
is the one I need to use.
Thanks again for your help.
Bob

"Biff" wrote:

Hi!

To solve the circular reference error, don't put the formula in column CX.

Try this:

=SUMPRODUCT(--(LEFT(CX1:CX100)="B"))
=SUMPRODUCT(--(LEFT(CX1:CX100)="O"))

You can't use entire columns as range arguments with Sumproduct (unless
you're using Excel 2007 beta).

Biff

"Bob" wrote in message
...
Column CX contains entries that either begin with a "B" or "O". I need to
count the number of cells whose contents begin with "B", and count the
number
of cells whose contents begin with "O".
I wrote the following formula, and even saved it as an array formula, but
I
keep getting a circular reference error:

=COUNTIF(CX:CX,LEFT(CX:CX,1)="B")

Any help would be greatly appreciated. Thanks.
Bob

Unfortunately, I need to put the formula in
column CX (for a variety of reasons).
=COUNTIF(CX:CX,"B*")
=COUNTIF(CX:CX,"O*")

Then you'll have to use a more reasonable range and put the formula outside
of that range otherwise you'll get the circular reference once again.

Biff

"Bob" wrote in message
...
Biff,
Thanks for the suggestion. Unfortunately, I need to put the formula in
column CX (for a variety of reasons).
It appears that the solution offered by Toppers (see the post after yours)
is the one I need to use.
Thanks again for your help.
Bob

"Biff" wrote:

Hi!

To solve the circular reference error, don't put the formula in column
CX.

Try this:

=SUMPRODUCT(--(LEFT(CX1:CX100)="B"))
=SUMPRODUCT(--(LEFT(CX1:CX100)="O"))

You can't use entire columns as range arguments with Sumproduct (unless
you're using Excel 2007 beta).

Biff

"Bob" wrote in message
...
Column CX contains entries that either begin with a "B" or "O". I need
to
count the number of cells whose contents begin with "B", and count the
number
of cells whose contents begin with "O".
I wrote the following formula, and even saved it as an array formula,
but
I
keep getting a circular reference error:

=COUNTIF(CX:CX,LEFT(CX:CX,1)="B")

Any help would be greatly appreciated. Thanks.
Bob

Biff,

......otherwise you'll get the circular reference once again...... <<

As soon as I read the above, I said to myself: "Totally agree."

It is interesting that Bob made it sound like Toppers' formula solved his circular reference problem.

I thought I missed something and I even tried ...... Sorry, I doubted you and me but Bob sounded so convincing. Anyway, I always experiment before I post.

The main reason for my post is that I am happy to know that for 2007 we can use a column/row for SUMPRODUCT.

Epinn

"Biff" wrote in message ...
Unfortunately, I need to put the formula in
column CX (for a variety of reasons).
=COUNTIF(CX:CX,"B*")
=COUNTIF(CX:CX,"O*")

Then you'll have to use a more reasonable range and put the formula outside
of that range otherwise you'll get the circular reference once again.

Biff

"Bob" wrote in message
...
Biff,
Thanks for the suggestion. Unfortunately, I need to put the formula in
column CX (for a variety of reasons).
It appears that the solution offered by Toppers (see the post after yours)
is the one I need to use.
Thanks again for your help.
Bob

"Biff" wrote:

Hi!

To solve the circular reference error, don't put the formula in column
CX.

Try this:

=SUMPRODUCT(--(LEFT(CX1:CX100)="B"))
=SUMPRODUCT(--(LEFT(CX1:CX100)="O"))

You can't use entire columns as range arguments with Sumproduct (unless
you're using Excel 2007 beta).

Biff

"Bob" wrote in message
...
Column CX contains entries that either begin with a "B" or "O". I need
to
count the number of cells whose contents begin with "B", and count the
number
of cells whose contents begin with "O".
I wrote the following formula, and even saved it as an array formula,
but
I
keep getting a circular reference error:

=COUNTIF(CX:CX,LEFT(CX:CX,1)="B")

Any help would be greatly appreciated. Thanks.
Bob

Anyway, I always experiment before I post.

I test about 50% of the time. Some posts don't really need testing. I
*ALWAYS* fully test the complex problems.

I am happy to know that for 2007 we can use a column/row for SUMPRODUCT.

That's both good and bad at the same time. You can currently use entire ROWS
but not columns. Testing 1 million+ rows when you're only using 2000 is a
huge waste of resources! It's a lot easier to type A:A than A1:A2000 but
there can be consequences!

I don't have the 2007 beta to play with but I'd be interested to see how
formulas handle entire columns in calculations.

Biff

"Epinn" wrote in message
...
Biff,

......otherwise you'll get the circular reference once again...... <<

As soon as I read the above, I said to myself: "Totally agree."

It is interesting that Bob made it sound like Toppers' formula solved his
circular reference problem.

I thought I missed something and I even tried ...... Sorry, I doubted you
and me but Bob sounded so convincing. Anyway, I always experiment before I
post.

The main reason for my post is that I am happy to know that for 2007 we can
use a column/row for SUMPRODUCT.

Epinn

"Biff" wrote in message
...
Unfortunately, I need to put the formula in
column CX (for a variety of reasons).
=COUNTIF(CX:CX,"B*")
=COUNTIF(CX:CX,"O*")

Then you'll have to use a more reasonable range and put the formula outside
of that range otherwise you'll get the circular reference once again.

Biff

"Bob" wrote in message
...
Biff,
Thanks for the suggestion. Unfortunately, I need to put the formula in
column CX (for a variety of reasons).
It appears that the solution offered by Toppers (see the post after yours)
is the one I need to use.
Thanks again for your help.
Bob

"Biff" wrote:

Hi!

To solve the circular reference error, don't put the formula in column
CX.

Try this:

=SUMPRODUCT(--(LEFT(CX1:CX100)="B"))
=SUMPRODUCT(--(LEFT(CX1:CX100)="O"))

You can't use entire columns as range arguments with Sumproduct (unless
you're using Excel 2007 beta).

Biff

"Bob" wrote in message
...
Column CX contains entries that either begin with a "B" or "O". I need
to
count the number of cells whose contents begin with "B", and count the
number
of cells whose contents begin with "O".
I wrote the following formula, and even saved it as an array formula,
but
I
keep getting a circular reference error:

=COUNTIF(CX:CX,LEFT(CX:CX,1)="B")

Any help would be greatly appreciated. Thanks.
Bob