>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
>> >
>>
>>
>>
|
Similar Threads
