Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif problems | Excel Worksheet Functions | |||
Column A=type, B=Model, how do I get Countif A=PC and B=Compaq | Excel Discussion (Misc queries) | |||
COUNTIF and automatic type conversion problem | Excel Discussion (Misc queries) | |||
COUNTIF and automatic type conversion problem | Excel Worksheet Functions | |||
Problems with DATE data type | New Users to Excel |