countif not working

I've used countif hundreds of times before, but now it has stopped working:

=COUNTIF(L11:O11,">c11") produces a 0 - apparently doesn't recognize cell
C11 - note that it remains lowercase in the countif statement rather than
automatically changing to uppercase (the correct calculated answer should be
2)

No cell reference between quotes is accepted.

substituting a real number for c11 functions properly.

I emailed this to a friend and it didn't work on his Excel either, proving
that it's not an application malfunction.

try it this way
=COUNTIF(L11:O11,">"&C11)

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Nick Krill" <NickKrill@discussions.microsoft.com> wrote in message
news:7DE7EC8D-CFFE-4F05-88F6-BCDCFC4F180A@microsoft.com...
> I've used countif hundreds of times before, but now it has stopped
> working:
>
> =COUNTIF(L11:O11,">c11") produces a 0 - apparently doesn't recognize cell
> C11 - note that it remains lowercase in the countif statement rather than
> automatically changing to uppercase (the correct calculated answer should
> be
> 2)
>
> No cell reference between quotes is accepted.
>
> substituting a real number for c11 functions properly.
>
> I emailed this to a friend and it didn't work on his Excel either, proving
> that it's not an application malfunction.

Thanks for the help – the expression ">"&C11 did work, but how do I restore
the standard functionality? I have hundreds of workbooks with thousands of
calculations that are formulated in the standard fashion. If you click on the
function dropdown list and allow Excel to create the formula for you it
creates it exactly the same way I’ve been using for years:
=COUNTIF(L11:O11,”>C11”) but can’t find cell C11




"Nick Krill" wrote:

> I've used countif hundreds of times before, but now it has stopped working:
>
> =COUNTIF(L11:O11,">c11") produces a 0 - apparently doesn't recognize cell
> C11 - note that it remains lowercase in the countif statement rather than
> automatically changing to uppercase (the correct calculated answer should be
> 2)
>
> No cell reference between quotes is accepted.
>
> substituting a real number for c11 functions properly.
>
> I emailed this to a friend and it didn't work on his Excel either, proving
> that it's not an application malfunction.

It's because Excel is trying to count the text string ">c11", however I
have all versions of excel from 95 to 2007 and that expression does not work
simply because it sees it as a text string so there is no such standard
fashion and with regards of using the fx to create a formula it will put
whatever you want since how would excel know that you are not looking to
count the text ">c11" ?

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Nick Krill" <NickKrill@discussions.microsoft.com> wrote in message
news:6B66A4DE-7A5E-401F-B20C-8573178EE097@microsoft.com...
> Thanks for the help - the expression ">"&C11 did work, but how do I
> restore
> the standard functionality? I have hundreds of workbooks with thousands of
> calculations that are formulated in the standard fashion. If you click on
> the
> function dropdown list and allow Excel to create the formula for you it
> creates it exactly the same way I've been using for years:
> =COUNTIF(L11:O11,">C11") but can't find cell C11
>
>
>
>
> "Nick Krill" wrote:
>
>> I've used countif hundreds of times before, but now it has stopped
>> working:
>>
>> =COUNTIF(L11:O11,">c11") produces a 0 - apparently doesn't recognize cell
>> C11 - note that it remains lowercase in the countif statement rather than
>> automatically changing to uppercase (the correct calculated answer should
>> be
>> 2)
>>
>> No cell reference between quotes is accepted.
>>
>> substituting a real number for c11 functions properly.
>>
>> I emailed this to a friend and it didn't work on his Excel either,
>> proving
>> that it's not an application malfunction.

Nick, the countif condition ">C11" has nothing to do with the contents of
cell C11. What you're checking for when the countif condition is ">C11" is
items in a list whose alphanumeric sort value is greater than that of the
text string "C11". For example, the string "C12" would be greater than "C11"
but "C10" wouldn't be (see the topic "Default sort order" in Excel Help for
more details). The solution provided by Don concatenates the value contained
in cell C11 into the countif condition.

"Nick Krill" wrote:

> Thanks for the help – the expression ">"&C11 did work, but how do I restore
> the standard functionality? I have hundreds of workbooks with thousands of
> calculations that are formulated in the standard fashion. If you click on the
> function dropdown list and allow Excel to create the formula for you it
> creates it exactly the same way I’ve been using for years:
> =COUNTIF(L11:O11,”>C11”) but can’t find cell C11
>
>
>
>
> "Nick Krill" wrote:
>
> > I've used countif hundreds of times before, but now it has stopped working:
> >
> > =COUNTIF(L11:O11,">c11") produces a 0 - apparently doesn't recognize cell
> > C11 - note that it remains lowercase in the countif statement rather than
> > automatically changing to uppercase (the correct calculated answer should be
> > 2)
> >
> > No cell reference between quotes is accepted.
> >
> > substituting a real number for c11 functions properly.
> >
> > I emailed this to a friend and it didn't work on his Excel either, proving
> > that it's not an application malfunction.