countif not working

N

Nick Krill

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.
 
N

Nick Krill

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
 
P

Peo Sjoblom

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
 
P

Paul Mathews

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.
 
Top