Count in a range of numbers

[Steve]

Is it possible to count the the number of numbers in a range within a
set of numbers....

Sounds confusing but essentailly what I am trying to do is count how
many times a number between 1 and 4 features in a sequence of numbers.
For example

2172394

In this case it would be 5 as there is are 2x2s 1x 3 and 1x 4.

Sometimes there would be anywhere from 1-7 numbers in a range of 0-9.
Ocassionaly there would be no numbers and ocassionally some letters in
a cell such as P or F so these would need to be ignored for example.

F217239

Is there a forumla that could calculate that automatically ?

Any help appreciated

Thanks

steve

 

[Claus Busch]

Hi Steve,

Am Thu, 30 Jan 2014 14:26:12 +0000 schrieb Steve:

2172394

In this case it would be 5 as there is are 2x2s 1x 3 and 1x 4.

Sometimes there would be anywhere from 1-7 numbers in a range of 0-9.
Ocassionaly there would be no numbers and ocassionally some letters in
a cell such as P or F so these would need to be ignored for example.

F217239

try it with a UDF:

Function CountNumbers(rngC As Range) As Integer
Dim myArr As Variant
Dim i As Integer

myArr = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
For i = LBound(myArr) To UBound(myArr)
If InStr(rngC, myArr(i)) > 0 Then
CountNumbers = CountNumbers + 1
End If
Next
End Function

If your string is in A1 then call the function in B1 with
=CountNumbers(A1)


Regards
Claus B.

 

[Steve]

Hi Claus, hope you are well and thanks for the super fast reply as
always.

I am getting a #NAME? error when i run the UDF is this because the
cell with the numbers in is pre-formatted as Number ?

Thanks

Steve

 

[Claus Busch]

Hi Steve,

Am Thu, 30 Jan 2014 14:50:04 +0000 schrieb Steve:

I am getting a #NAME? error when i run the UDF is this because the
cell with the numbers in is pre-formatted as Number ?

have you pasted the code into a standard module?
Please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "CountNumbers"
Download the file because macros are disabled in SkyDrive


Regards
Claus B.

 

[Steve]

Hi Claus, I have donwloaded and maaged to get it to work on your
spreadsheet. However, what I was badly explaingin was that the module
needs to count the number of instances a number appears in the
range...

So in your example for

A2 which has 111222 the result shoulkd be 6 as there are 6 numbers
between the range of 1-4

or if the number string was 234567 the answer would be 3 as there are
threee numbers between 1 and 4

Sorry that wasnt explained better and thanks for your continued help.

Regards


Steve

 

[Claus Busch]

Hi again,

Am Thu, 30 Jan 2014 16:19:21 +0100 schrieb Claus Busch:

=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,),2,),3,),4,))

or try it with:
=SUMPRODUCT((MID(A1,COLUMN(1:1),1)={"1";"2";"3";"4"})*1)


Regards
Claus B.

 

[Steve]

Perfect - Thanks again

 

[Steve]

Hi Claus, both perfect - thanks Steve

 

[Ron Rosenfeld]

Is it possible to count the the number of numbers in a range within a
set of numbers....

Sounds confusing but essentailly what I am trying to do is count how
many times a number between 1 and 4 features in a sequence of numbers.
For example

2172394

In this case it would be 5 as there is are 2x2s 1x 3 and 1x 4.

Sometimes there would be anywhere from 1-7 numbers in a range of 0-9.
Ocassionaly there would be no numbers and ocassionally some letters in
a cell such as P or F so these would need to be ignored for example.

F217239

Is there a forumla that could calculate that automatically ?

Any help appreciated

Thanks

steve

You could also try (with no real limitation as to the length of the number):

This formula must be **array-entered**:

=SUM((IFERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),-10)>=MinNum)*
(IFERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),-10)<=MaxNum))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

 

[Steve]

Sorry for the huge delay in replying Ron as I have not been here for
some time but thank you so much for taking the time to respond

Much apreciated

Cheers

Steve