Count in a range of numbers


S

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
 
Ad

Advertisements

C

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.
 
S

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
 
S

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
 
C

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.
 
Ad

Advertisements

Ad

Advertisements

S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top