# InStr function that counts all the numbers in a cell that fall within a specified range

I have cells with values like: 19A, 20B, 26B,7C,20B,35D,14C, 21A,25C,26B,31A,17B  and i need a macro that tells me how many occurrences there are of numbers between 20 and 25.

I could split the strings into separate cells, and then create a helper row that strips out the letters. and use this:
[DQ3] = WorksheetFunction.CountIf([DR5:EH5], ">19") - WorksheetFunction.CountIf([DR5:EH5], ">25")

but I really need to do it as an InStr without going trough all that.

Thanks,
John
LVL 1
###### Who is Participating?

Commented:
Something like this?
sample.xlsm
0

Solutions ConsultantCommented:
This formula will do what you want

=SUM(COUNTIF(A1:A2, "*19*"),COUNTIF(A1:A2, "*20*"),COUNTIF(A1:A2, "*21*"),COUNTIF(A1:A2, "*22*"),COUNTIF(A1:A2, "*23*"),COUNTIF(A1:A2, "*24*"),COUNTIF(A1:A2, "*25*"))

The trick here is the use of the wildcard * in the countif condition

Michael
0

Solutions ConsultantCommented:
Sorry forgot to change the range in my formula. Here it is with the correct range input

=SUM(COUNTIF(DR5:EH5, "*19*"),COUNTIF(DR5:EH5, "*20*"),COUNTIF(DR5:EH5, "*21*"),COUNTIF(DR5:EH5, "*22*"),COUNTIF(DR5:EH5, "*23*"),COUNTIF(DR5:EH5, "*24*"),COUNTIF(DR5:EH5, "*25*"))

Michael
0

Reliability Business Tools Analyst IIAuthor Commented:
Michael, thanks for the formulas but the number ranges are dynamic and sometimes very large so this probably won't work very well for me.

m4trix, your function is awesomely simple, and I was able to make a dynamic macro out of it which i will post here tomorrow if you're interested.

Thanks!
John
0

Commented:
Thanks, and sure, post away! Perhaps someone down the road will find it helpful
0

Reliability Business Tools Analyst IIAuthor Commented:
Here ya go. Thanks again.

- John
``````ges()
Dim cel As Range, lft As Range, rgt As Range, rng As Range
For Each cel In [AllY]
Set lft = cells(cel.Row, [DR1].Column)
Set rgt = lft.End(xlToRight)
If lft = "" Then GoTo here
If lft.Offset(0, 1) = "" Then
Set rng = lft
Else
Set rng = Range(lft, rgt)
End If
cel = countValues(rng, cells(cel.Row, [Y_Lo].Column), cells(cel.Row, [Y_Hi].Column))
here:
Next cel
End Sub
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.