COUNTIF() data type problems
 

Hello-

I am attempting to use the COUNTIF() function in Microsoft Excel 2002,
but I'm seeing some unexpected behavior. The problem arises when trying to
count numbers - Excel seems not to be treating the data as it should.

Basically, a simple example like: =COUNTIF($A8:$A20, "1.1") where the
cells $a8:$a20 are all "text" type should only match increment if each cell
matches 1.1 exactly. However, it seems to match "1.1" and "1.10" and
"1.100" etc. I would expect this to be the case if the cell types were
numbers or decimals, but not strings, since the string "1.1" is completely
different than the string "1.10".

Can anybody verify this, and if so, provide a solution? Thanks!

Kris

COUNTIF() data type problems
 

try:

=SUM(N(A1:A100="1.1"))
entered as array (ctrl-shift-enter)

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Kris" wrote:

Hello-

I am attempting to use the COUNTIF() function in Microsoft Excel
2002,
but I'm seeing some unexpected behavior. The problem arises when
trying to count numbers - Excel seems not to be treating the data as
it should.

Basically, a simple example like: =COUNTIF($A8:$A20, "1.1") where
the
cells $a8:$a20 are all "text" type should only match increment if each
cell matches 1.1 exactly. However, it seems to match "1.1" and "1.10"
and "1.100" etc. I would expect this to be the case if the cell types
were numbers or decimals, but not strings, since the string "1.1" is
completely different than the string "1.10".

Can anybody verify this, and if so, provide a solution? Thanks!

Kris

COUNTIF() data type problems
 

That actually fixed the problem. Thank you very much. Would you mind
quickly explaining why this works and why the other method doesn't work?
Thanks!

kris


"keepitcool" wrote in message
...
try:

=SUM(N(A1:A100="1.1"))
entered as array (ctrl-shift-enter)

keepITcool

COUNTIF() data type problems
 

I cant.. I tried your option..

countif uses microsofts "standard" criteria argument.. which makes it
flexible but in this case too flexible.

So I tried something else & posted it.
While working with arrays I often use the N() function to force an array
of true/false/missing to numbers only..


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Kris" wrote:

That actually fixed the problem. Thank you very much. Would you mind
quickly explaining why this works and why the other method doesn't
work? Thanks!

kris


"keepitcool" wrote in message
...
try:

=SUM(N(A1:A100="1.1"))
entered as array (ctrl-shift-enter)

keepITcool