COUNTIF() data type problems

[Kris]

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

 

[keepitcool]

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]

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]

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 >