Count IF question

[galenweaver]

I have a column of data, many of the numbers in the column have the same first 3 digits. I want to count the number of times a value occurs in the column with the same first 3 digits.

Here is the formula I am using:=COUNTIF($A$1:$A$1117,"116*")

It returns "0" as the value and I can't figure out why it won't work. I want to see how many timed times 116 occurs in the A column.

Thanks,
Galen

 

[VoG]

Try

=SUMPRODUCT(--(LEFT($A$1:$A$1117,3)="116"))

 

[Jonmo1]

Welcome to the board...

It's because the values in column A are numbers, but "116*" is a text string
Even though the * is a wildcard, simply adding the "'s makes it a text string..


Try
=SUMPRODUCT(--(LEFT($A$1:$A$1117,3)="116"))


Hope that helps.

 

[galenweaver]

That worked. Now I want to use a value in a cell in place of the 116, any ideas?

And just so you know this forum rocks! You guys are great!
I can't believe I got 2 solutions in 5 minutes.

 

[VoG]

With 116 in D1 try

=SUMPRODUCT(--(LEFT($A$1:$A$1117,3)=D1&""))

 

[galenweaver]

I love this forum!!!! You guys just saved me about 2 hours of work. Thanks a million.