Need help with COUNTIF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 31 Oct 2006 22:19 GMT

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

Reply to this Message

Biff - 31 Oct 2006 22:28 GMT

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

> 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
[quoted text clipped - 8 lines]
> Any help would be greatly appreciated. Thanks.
> Bob

Reply to this Message

Bob - 31 Oct 2006 22:51 GMT

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

> Hi!
>
[quoted text clipped - 22 lines]
> > Any help would be greatly appreciated. Thanks.
> > Bob

Reply to this Message

Biff - 31 Oct 2006 23:00 GMT

>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

> Biff,
> Thanks for the suggestion. Unfortunately, I need to put the formula in
[quoted text clipped - 33 lines]
>> > Any help would be greatly appreciated. Thanks.
>> > Bob

Reply to this Message

Epinn - 31 Oct 2006 23:35 GMT

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

>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

> Biff,
> Thanks for the suggestion. Unfortunately, I need to put the formula in
[quoted text clipped - 35 lines]
>> > Any help would be greatly appreciated. Thanks.
>> > Bob

Reply to this Message

Biff - 01 Nov 2006 00:15 GMT

>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

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

>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

> Biff,
> Thanks for the suggestion. Unfortunately, I need to put the formula in
[quoted text clipped - 35 lines]
>> > Any help would be greatly appreciated. Thanks.
>> > Bob

Reply to this Message

Toppers - 31 Oct 2006 22:34 GMT

Try:

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

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

> 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
[quoted text clipped - 6 lines]
> Any help would be greatly appreciated. Thanks.
> Bob

Reply to this Message

Bob - 31 Oct 2006 22:49 GMT

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

> Try:
>
[quoted text clipped - 12 lines]
> > Any help would be greatly appreciated. Thanks.
> > Bob

Reply to this Message