Stuck on countif

[caliroom]

I can't seem to get a count if formula to work. I have a range of cells see below.

I would like to count the number of times a value is in a cell such as "01" or "24"

The formula returns zero count because I believe it is looking for only the "01" or "24" and if the cell contains other numbers or characters it will pass the cell in the count if formula. am i using the wrong formula or not structuring the count if correctly? I also notice the formula drops the zero on 01 and not sure if that is an issue too.

Thank you for any help...
Jeff


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01-03-15-20-22-24-25-37-39-42-43-45-50-58-63-70-74-75-78-80[/TD]
[TD]**[/TD]
[TD]**[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]05-06-15-19-24-25-26-27-30-31-32-42-52-59-60-67-68-75-76-79[/TD]
[TD]**[/TD]
[TD]**[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]04-08-09-14-22-25-34-39-40-43-55-57-58-59-61-64-66-70-71-76[/TD]
[TD]**[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the forum.

Try:

Book1
	A	B	C	D
1	01-03-15-20-22-24-25-37-39-42-43-45-50-58-63-70-74-75-78-80		Code	Count
2	05-06-15-19-24-25-26-27-30-31-32-42-52-59-60-67-68-75-76-79		01	1
3	04-08-09-14-22-25-34-39-40-43-55-57-58-59-61-64-66-70-71-76		15	2
4			25	3
5			75	2
6			77	0
Sheet1

Cell Formulas
Range	Formula
D2	=COUNTIF(A$1:A$5,"*"&C2&"*")

Firstly, you need to select column C (in this example) and change the format to Text. That will prevent Excel from changing 01 to 1. Next, to check for a specific number within some text, you need to add wildcards before and after the number. The "*" means match anything.

If you don't want to format column C as text, you can use this formula:

=COUNTIF(A$1:A$5,"*"&TEXT(C2,"00")&"*")

 

[caliroom]

Thank you!! Works great!

 

[Eric W]

You're welcome!