Need help with COUNTIF

=?Utf-8?B?Qm9i?=
Guest
Posts: n/a

 31st Oct 2006
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
Guest
Posts: n/a

 31st Oct 2006
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" <(E-Mail Removed)> wrote in message
news:4D728A58-1707-4963-B4F0-(E-Mail Removed)...
> 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
>

=?Utf-8?B?VG9wcGVycw==?=
Guest
Posts: n/a

 31st Oct 2006
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
>

=?Utf-8?B?Qm9i?=
Guest
Posts: n/a

 31st Oct 2006
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
> >

=?Utf-8?B?Qm9i?=
Guest
Posts: n/a

 31st Oct 2006
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.
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" <(E-Mail Removed)> wrote in message
> news:4D728A58-1707-4963-B4F0-(E-Mail Removed)...
> > 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
Guest
Posts: n/a

 31st Oct 2006
>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" <(E-Mail Removed)> wrote in message
news:0CB9D6B3-768B-4B82-ABD5-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:4D728A58-1707-4963-B4F0-(E-Mail Removed)...
>> > 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
>> >

>>
>>
>>

Epinn
Guest
Posts: n/a

 31st Oct 2006
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" <(E-Mail Removed)> wrote in message news:eX0\$7fT\$(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:0CB9D6B3-768B-4B82-ABD5-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:4D728A58-1707-4963-B4F0-(E-Mail Removed)...
>> > 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
Guest
Posts: n/a

 1st Nov 2006
>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" <(E-Mail Removed)_SPAM> wrote in message
news:ertEp3T\$(E-Mail Removed)...
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" <(E-Mail Removed)> wrote in message
news:eX0\$7fT\$(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:0CB9D6B3-768B-4B82-ABD5-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:4D728A58-1707-4963-B4F0-(E-Mail Removed)...
>> > 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
>> >

>>
>>
>>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post sctroy Microsoft Excel Misc 2 25th Sep 2005 04:13 AM =?Utf-8?B?RWxsaWU=?= Microsoft Excel Worksheet Functions 4 15th Sep 2005 10:06 PM =?Utf-8?B?Sm9ubmllUA==?= Microsoft Excel Worksheet Functions 3 22nd Feb 2005 03:55 PM maswinney Microsoft Excel Worksheet Functions 3 16th Nov 2004 12:06 AM -\$- Windows XP Internet Explorer 2 21st Dec 2003 11:45 PM

Features