Countif range contains part of a value

[antwaan]

I have a range of cells in column A. The values may range from:
10041, 10041B to 10041PEM

I need a formula to count the number of "41" that occurs. Please help!

 

[Jacob Skaria]

=COUNTIF(A:A,"*41*")

If this post helps click Yes

 

[Glenn]

I have a range of cells in column A. The values may range from:
10041, 10041B to 10041PEM

I need a formula to count the number of "41" that occurs. Please help!

Any "41", like "10412" and "14102PEM"? What about "14141B"...is that counted
once or twice?

Or are you looking for only where "41" are the 4th and 5th characters in the cell?

 

[antwaan]

Thanks Jacob, but this formula does not produce accurate results.

 

[antwaan]

"41" will always be the 4th & 5th characters in the cell.

 

[Jacob Skaria]

Some of the cells may be in number format.

Suppose A is the column you have with this data..
in Col B enter the formula B1 = TEXT(A1,"?")
and then try

=COUNTIF(B:B,"*41*")


If this post helps click Yes

 

[Glenn]

Try this:

=SUMPRODUCT(--(MID(A1:A100,4,2)="41"))

"41" will always be the 4th & 5th characters in the cell.

 

[Jacob Skaria]

Oops
B1 = T(A1). Copy that down...which will convert the values to text and then
apply CountIF

If this post helps click Yes

 

[antwaan]

=TEXT(A1,"?") worked, =T(A1) didn't.

Thank you Jacob!

 

[antwaan]

Glenn - this worked great. Now I don't have to add in another column to
convert to text; plus it's more accurate.
Thanks again.

 

[Glenn]

Glad I could help.

Your responses here will almost always be more accurate when you provide more
information. Your original request was open to many interpretations and
possible solutions.