Solved Using MS Excel Countif and Mid

Tags:
Microsoft Excel 2010 - complete product...
excel
June 29, 2013 at 06:06:30
Specs: Windows XP Pro SP3, 3.0 Mz/1.0Gb
I need to count the number of times a particular letter is in the third place of a string of letters and numbers. These strings are in Column A to M, rows 1 to 650.

I have tried combining = Countif and = Mid in different ways but have been unsuccessful.

Thank you.
Brian W


See More: Using MS Excel Countif and Mid

Report •

✔ Best Answer
June 29, 2013 at 18:24:07
Try this, it's a bit of a "cheat",
as it uses both =COUNTIF() and Wild Card characters.

It seems to work, but I have not done extensive testing:

=COUNTIF(A1:M650,"??X*")

The X is the single character, in the Third position that your looking for,
the first two ? characters are the wild card for Any Single Character,
the trailing * means Any Character, Any Number of Times.

As best as I can tell this only works for TEXT,
if the cells your checking are ALL NUMBERS then it above will not work,
or at least I could not get it to work with ONLY numbers.


MIKE

http://www.skeptic.com/


See in Thread ↓

#1
June 29, 2013 at 08:51:21
I tried a couple of array formulas, but nothing seemed to work consistently.

If you don't mind a User Defined Function, this should work:

Function countD(ByVal rng As Range)
 Application.Volatile
 For Each cell In rng
   If Mid(cell, 3, 1) = "d" Then
     tmpCount = tmpCount + 1
   End If
 Next
  countD = tmpCount
End Function

After pasting this code into a standard VBA module, enter =countD(A1:M650) in any cell and it will count each "3rd position d" in the range entered.

If it will always be A1:M650, then use this and just enter =countD()

Function countD()
 Application.Volatile
 For Each cell In Range("A1:M650")
   If Mid(cell, 3, 1) = "d" Then
     tmpCount = tmpCount + 1
   End If
 Next
  countD = tmpCount
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 29, 2013 at 12:38:43
Dear DerbyDad03,

Thank you for your response. I have never used a User Defined Function so I do not know how to enter the code that you provided. Can you give me any references so that I can learn how to use it?

Thanks again,
Brian W


Report •

#3
June 29, 2013 at 14:51:11
Look here:

http://office.microsoft.com/en-us/e...

It's more than you probably need, but it gives a pretty complete picture of UDF's.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions
See More ↓

#4
June 29, 2013 at 18:24:07
✔ Best Answer
Try this, it's a bit of a "cheat",
as it uses both =COUNTIF() and Wild Card characters.

It seems to work, but I have not done extensive testing:

=COUNTIF(A1:M650,"??X*")

The X is the single character, in the Third position that your looking for,
the first two ? characters are the wild card for Any Single Character,
the trailing * means Any Character, Any Number of Times.

As best as I can tell this only works for TEXT,
if the cells your checking are ALL NUMBERS then it above will not work,
or at least I could not get it to work with ONLY numbers.


MIKE

http://www.skeptic.com/


Report •

#5
June 30, 2013 at 15:32:33
DerbyDad03,

Thank you for your assistance; I will read the info you cited about User Defined Functions and will try to use them the next time I run into a similar problem.

Brian W


Report •

#6
June 30, 2013 at 15:35:00
Dear Mike,

Thank you for your assistance. The strings that I am using contain text and your solution worked perfectly.

Brian W


Report •
Start a discussion

Ask Question