Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2011-09-22
Medium Priority
332 Views
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
0
Question by:gabrielPennyback
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2

LVL 7

Accepted Solution

m4trix earned 2000 total points
ID: 36584709
Something like this?
sample.xlsm
0

LVL 23

Expert Comment

ID: 36584729
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

LVL 23

Expert Comment

ID: 36584734
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

LVL 1

Author Comment

ID: 36584969
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

LVL 7

Expert Comment

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

LVL 1

Author Comment

ID: 36590413
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month7 days, 23 hours left to enroll