Need some help with a COUNTIF function

[KimonH]

I am working with fairly large sheets (>800k lines) where I am trying
to get a count of cells that will have a certain format : specifically
they need to contain no letters (only numbers ) but the trick is some
of the valid numbers (that also need to be counted) are in the format
of i.e "098646,01" .
Tried using =COUNTIF(A2:A7,"<>"&"*") but the presence of the ","
character seems to be throwing the formula off.

Ideally I would like to also exclude blanks and validate whether the
number is over 12 characters long (including the ",").
Anyone have any ideas? Would be appreciated as I'm stuck on this ....
Thank you !

K.

 

[Pete]

I think you want the oposite of this. Good place to start.

http://groups.google.com/group/micr...ll+characters+except+letters#d5a99d23d65637e7

Pete

 

[Charabeuh]

Hello,

I asusmed your data are in A1:A7

You could insert an empty column before the column "B" that will
contains the formula to verify whether the cell of the column "A"
matches or not the conditions you want to verify.

In the new column "B", into B2 put the following formula:
_________________________________________________________________
=ISNUMBER(SUM(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),
", 0123456789")))
_________________________________________________________________
This formula is an array formula. You have to validate it with the
three keystrokes Ctrl+Shift+Enter instead with the single keystroke
Enter.
The formula will be surrounded with braces if it is correctly validated
as an array formula.

Then drag down this formula to cell B7.

To count the cells in (A2:A7) that match the condition, inpout into a
cell the formula:=COUNTIF(B2:B7,TRUE)

----
Somme examples of the result of the array formula:
098 646,01 False
99,,646,01 True
100 646,0AAA1 False
False
101 64601 False
102646,789 True
123,,456B False

If spaces are not valid letters, then in the formula replace :
", 0123456789" with ",0123456789")

If you want only words that have less or equal than 12 charaters, add a
condition in the formula. ex:
_______________________________________________________________________
=AND(ISNUMBER(SUM(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),
",0123456789"))),LEN(A2)<=12)
_______________________________________________________________________
(also an array formula)

Hope it will help you,


KimonH a utilisé son clavier pour écrire :

 

[Charabeuh]

Hello,

I asusmed your data are in A1:A7

You could insert an empty column before the column "B" that will
contains the formula to verify whether the cell of the column "A"
matches or not the conditions you want to verify.

In the new column "B", into B2 put the following formula:
_________________________________________________________________
=ISNUMBER(SUM(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),
", 0123456789")))
_________________________________________________________________
This formula is an array formula. You have to validate it with the
three keystrokes Ctrl+Shift+Enter instead with the single keystroke
Enter. The formula will be surrounded with braces if it is correctly
validated
as an array formula.

Then drag down this formula to cell B7.

To count the cells in (A2:A7) that match the condition, inpout into a
cell the formula:=COUNTIF(B2:B7,TRUE)

----
Somme examples of the result of the array formula:
098 646,01 False
99,,646,01 True
100 646,0AAA1 False
False
101 64601 False
102646,789 True
123,,456B False

If spaces are not valid letters, then in the formula replace :
", 0123456789" with ",0123456789")

If you want only words that have less or equal than 12 charaters, add
a condition in the formula. ex:
_______________________________________________________________________
=AND(ISNUMBER(SUM(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),
",0123456789"))),LEN(A2)<=12)
_______________________________________________________________________
(also an array formula)

Hope it will help you,






KimonH a utilisé son clavier pour écrire :

 

[Charabeuh]

Sorry !

I made a mess with the two formulas (with including or not space as a
valid character)

The example I have given match the formula where spaces are not valid
characters.




Charabeuh a couché sur son écran :