Find & CountIf returning different results?

[Christopher135]

Hi Guys,

I have a list of phone numbers, I want to count the number of 13 and 1300 numbers called.

Please Login or Register  to view this content.

returns 0. Which I know is wrong.

when performing a ctrl+F Find all with the same search criteria (13*, 1300*) it finds the correct data/numbers.

I'm lost and confused?

[benishiryo]

hi Christopher. using the asterisk only works for texts. and even if it works for numbers, you will be double counting them.
1311
13001
the above would be 3 counts (2 of them starts with 13, 1 of them start with 1300)

so try:
=SUMPRODUCT((LEFT(K4:K99999,2)="13")*(MID(K4:K99999,3,2)<>"00")+(LEFT(K4:K99999,4)="1300"))

SUMPRODUCT is a little heavy on resources. so don't range up that much if you don't need to

[Christopher135]

Again and again I'm amazed at what you guys can do with excel. That works perfectly perfectly perfectly! I will keep in mind what you said about range, maybe learn how to code to the first empty cell in a range to the internets!

[benishiryo]

=) glad to help. anyways, i was in a rush earlier & just wanted to give you a solution that substitutes what you were trying to do. thinking it over, i couldn't visualize a scenario you have that will fail if you simply do:
=SUMPRODUCT(--(LEFT(K4:K99999,2)="13"))

since 1300 also begins with "13". unless you just want those beginning wit "1300", then you would have to use:
=SUMPRODUCT(--(LEFT(K4:K99999,4)="1300"))

my formula in post #2 will work. but it's just unnecessary to put in so many arguments.