How to count rows in a column whos 1st 6 character contain

[Suser]

Hi,

How do you count rows in a column that contain 2205, 3959 or 3858 only if the first 4 digits are one of this three numbers?

The column contains either 13 or 15 character numbers. But, each number is preceded by =" and ends with ".

Two example cells: A2 and A3

="3959506134974"
="2205253647730"

I tried
=IF(LEFT($A:$A,4) = "2205", COUNTIF($A:$A,"*2205*"))
=IF(LEFT($A:$A,4) = "3959", COUNTIF($A:$A,"*3959*"))
=IF(LEFT($A:$A,4) = "3858", COUNTIF($A:$A,"*3858*"))

but it seems to work for 2205 but not for the other two numbers, I am not sue why.

I also tried
=IF(LEFT($A:$A,4) = "3959", COUNTIF($A:$A,"*3959*"))
but it returned FALSE.

Any help. Thanks

 

[JoeMo]

Excel Workbook
	A	B
2	3959506134974	3
3	2205253647730
4	385811111111
5	1111111111
Combined

 

[Suser]

Thank you, Joe! The formula works! I modified a bit so that I get the count in separate cells.

[TABLE="width: 784"]
<colgroup><col width="334" style="width: 251pt; mso-width-source: userset; mso-width-alt: 5350;" span="2"> <col width="376" style="width: 282pt; mso-width-source: userset; mso-width-alt: 6016;"> <tbody>[TR]
[TD="class: xl65, width: 334, bgcolor: transparent"]2205s[/TD]
[TD="class: xl65, width: 334, bgcolor: transparent"]3959s[/TD]
[TD="class: xl65, width: 376, bgcolor: transparent"]3858s[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, align: left"]=SUMPRODUCT(--(LEFT($A2:$A16,4)+0={2205}))[/TD]
[TD="class: xl66, bgcolor: yellow, align: left"]=SUMPRODUCT(--(LEFT($A2:$A16,4)+0={3959}))[/TD]
[TD="class: xl66, bgcolor: yellow, align: left"]=SUMPRODUCT(--(LEFT($A2:$A16,4)+0={3858}))[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to make the formulas count any number of row and not just $A2:$A11? I tried (LEFT($A:$A,4) but I got #VALUE! error. A1 is a heading.

I appreciate the help, thanks!

 

[JoeMo]

Thank you, Joe! The formula works!
[TABLE="width: 784"]
<tbody>[TR]
[TD="class: xl65, width: 334, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 376, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, align: left"][/TD]
[TD="class: xl66, bgcolor: yellow, align: left"][/TD]
[TD="class: xl66, bgcolor: yellow, align: left"]
[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to make the formulas count any number of row and not just $A2:$A11? I tried (LEFT($A:$A,4) but I got #VALUE! error. A1 is a heading.

I appreciate the help, thanks!

You are welcome. The formula returns an error if there are any blank or non-numeric cells in the specified range. Quick way to extend the row address is to put a 0 in every blank cell you want to include. So, in the example of post #2 the data are in A2:A5. If you anticipate the data could eventually expand to say A25 then put 0's in A6:A25 and change the formula to: =SUMPRODUCT(--(LEFT(A2:A25,4)+0={2205,3959,3858}))

 

[Suser]

Thanks again, Joe! That will work.

Someone also suggested this and repeat it for each number, which seems to work as well
=COUNTIF($A:$A,"2205*")