Silahkan dipelajari dan dimodifikasi file terlampir @Mardana... Kunci pencarian datanya ada pada definisi nama kode prefiks provider-nya. Jika ada kode dari provider tertentu yang belum masuk ke dalam daftar, cukup ubah atau tambahkan pada definisi nama prefiks sesuai provider-nya. Demikian juga jika ingin menambahkan daftar kode prefiks provider baru, buat definisi nama sesuai keinginan, kemudian isi atau edit langsung kode prefiksnya. Baiknya setiap kode dipisahkan dengan karakter tertentu (misalnya, saya menggunakan karakter | -- karakter pipa atau pipe character). Contohnya:

Pada gambar di atas, nama Prefix.All merupakan gabungan seluruh kode prefiks provider lainnya. Tujuannya untuk mencari data kode prefiks provider yang tidak ada dalam daftar. Demikian yang bisa saya bantu dan saya jelaskan. Semoga sesuai... ;)

Oh... Sori. Untuk Excel 2003, coba lihat pada menu INSERT > NAME > DEFINE. Gambarannya:

Jika bermasalah dengan Defined Name Range, solusinya bisa menggunakan sel biasa. Misalkan saja pada sheet DATA, di sel L1 untuk prefiks Telkomsel, sel L2 untuk prefiks Indosat dan seterusnya. Isi nilainya saja, yakni di sel L1 diisi:

="811|812|813|821|822|852|853|823|851"

Demikian pula dengan definisi nama lainnya. Kemudian pada formula, diubah acuannya. Misalnya untuk sel D4 (kolom Telkomsel) formula sebelumnya:

=SUMPRODUCT((ISNUMBER(SEARCH(LEFT(Table.Data[A];3);Prefix.Telkomsel)))*1)

diubah menjadi:

=SUMPRODUCT((ISNUMBER(SEARCH(LEFT(Table.Data[A];3);$L$1)))*1)

Demikian pula dengan kolom provider lainnya... ;)

Coba periksa kembali pada notasi fungsi LEFT(A:4). Seharusnya pada fungsi LEFT tersebut, argumen sumber teks dan argumen jumlah karakter yang diambil dipisahkan dengan tanda koma atau titik koma (tergantung kepada pengaturan Regional Setting pada komputer). Kemudian, perhatikan juga notasi range acuannya. Jika acuan teks sumbernya berupa sel, notasinya adalah indeks kolom disambung dengan indeks baris (contoh: A5, B100), atau jika acuannya berupa range, maka notasi sel asal dan sel akhir dari teks sumbernya dipisahkan oleh tanda titik dua (contoh: A5:A25, B1:B5). Jadi seharusnya:

... LEFT(A4,3) ... atau ... LEFT(A4;3) ...

Kemudian, agar jumlah kode prefiks per provider dapat dihitung dengan tepat, acuan teks sumber pada fungsi LEFT yang digunakan di dalam setiap formula (misalkan formula pada sel D4) harus berupa range, buka sel. Jadi acuannya ke range data, tepatnya pada kolom Nomor Telepon, misalnya range datanya A4:A24 dan range rujukan Kode Prefiks-nya pada sel L1, maka formulanya akan menjadi:

=SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$1)))*1)

Perhatikan juga bahwa notasi sel dan range acuan harus dibuat absolut (ditandai dengan tanda dolar). Dan satu hal lagi, untuk nomor telepon yang kode prefiksnya tidak diketahui providernya (semisalnya nomor telepon luar negeri), tidak perlu dibuatkan definisinya (kecuali memang dibutuhkan). Coba lihat file yang saya lampirkan atau pada gambar Name Manager di atas. Nama Prefix.All merupakan gabungan dari nama kode prefiks provider lainnya. Jadi semisalnya kode prefiks dari masing-masing provider disusun pada sel (asumsikan mulai sel L1 sampai dengan sel L6, maka sel L7 merupakan gabungan isi dari range L1:L6 (bisa digabungkan dengan formula atau secara manual). Isi sel L7 ini nantinya digunakan di dalam formula untuk menghitung nomor luar negeri (pada gambar @Mardana di atas, pada sel I4).

Silahkan coba disusun kembali formulanya dan lihat apakah kalkulasinya benar. Demikian yang bisa saya jelaskan... ;)

Saya coba jelaskan kembali agar mbak/mas @Mardana bisa lebih memahami formulanya. Formula yang digunakan intinya mencari kecocokan kode prefiks dari setiap nomor telepon terhadap kode prefiks setiap provider yang telah ditentukan.

=SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$1)))*1)

Oleh karena kode prefiks terdiri dari 3 karakter/angka, maka kita asumsikan bahwa setiap baris data nomor telepon dimulai dengan 3 karakter/angka kode prefiks tersebut. Oleh karena itu, ada 2 kelompok data yang akan kita bandingkan: data Nomor Telepon dan data Kode Prefiks Per Provider. Dalam hal ini, misalnya ditentukan data Nomor Telepon berada pada range A4:A24 dan data Kode Prefiks ditentukan pada sel L1 sampai L7.

Untuk mengambil 3 karakter dari data Nomor Telepon, kita menggunakan fungsi LEFT. Normalnya, argumen Text pada fungsi LEFT hanya mengacu kepada sel tunggal atau diisi dengan teks langsung. Namun dalam kasus ini, karena kita ingin menghitung jumlah baris data Nomor Telepon yang sesuai dengan provider tertentu, maka argumen Text pada fungsi LEFT harus kita rujuk kepada range data Nomor Telepon, sehingga formulasinya menjadi:

LEFT($A$4:$A$24;3)

Formula di atas akan menghasilkan Array berupa 3 karakter/angka dari setiap baris data Nomor Telepon. Berikutnya, untuk setiap nilai yang dihasilkan oleh fungsi LEFT tersebut, akan diperiksa apakah ada di dalam daftar kode prefiks dari provider yang akan kita periksa.

Oleh karena fungsi LEFT pada dasarnya akan menghasilkan nilai dengan tipe data Text, maka triknya kita bisa menggunakan fungsi SEARCH untuk mencari kecocokan setiap nilai yang dihasilkan fungsi LEFT di dalam teks daftar kode prefiks. Untuk itu, daftar kode prefiks dari setiap provider kita susun dalam sebuah teks dan diletakkan pada sebuah sel (atau Named Range) untuk masing-masing provider. Sederhananya: L1 untuk kode prefiks Telkomsel, L2 untuk kode prefiks Indosat, L3 untuk kode prefiks XL dan seterusnya. Jika ada 6 provider yang diketahui kode prefiksnya, maka berarti sel yang terisi adalah sel L1 sampai dengan sel L6.

Lalu bagaimana dengan Nomor Telepon yang tidak ada kode prefiksnya (misalnya untuk nomor telepon luar negeri)?

Fungsi SEARCH yang kita gunakan pada formula akan mencari apakah setiap nilai yang dihasilkan oleh fungsi LEFT ada di dalam teks daftar kode prefiks sesuai provider yang kita rujuk. Apabila kode prefiks ditemukan, maka fungsi SEARCH akan mengembalikan nilai berupa posisi awal teks yang dicari, jika tidak maka akan menghasilkan nilai kesalahan #VALUE! Pada dasarnya, kita tidak butuh nilai posisi awal teks tersebut. Kita hanya ingin tahu, apakah nilai kode prefiks yang diambil fungsi LEFT ada pada daftar kode prefiks dari povider yang kita rujuk. Oleh karena itu, triknya kita gunakan fungsi ISNUMBER untuk mendapatkan hasil apakah fungsi SEARCH menghasilkan nilai atau tidak.

Fungsi ISNUMBER sendiri akan menghasilkan nilai TRUE atau FALSE, yang tidak dapat dihitung secara langsung. Agar dapat dihitung, maka kita kita paksa agar nilai yang dihasilkan fungsi ISNUMBER menjadi numerik dengan menambahkan notasi *1. Dengan demikian, TRUE*1 = 1 dan FALSE*1=0. Nah, sejauh ini kita sudah akan mendapatkan senarai (Array) nilai berupa 1 jika kode prefiks ditemukan atau 0 jika kode prefiks tidak ada di dalam daftar.

Fungsi SUMPRODUCT merupakan proses akhir yang akan kita gunakan untuk menjumlahkan senarai nilai yang dihasilkan oleh fungsi ISNUMBER. Fungsi ini akan menghasilkan satu nilai saja yang merupakan hasil akhir proses kalkulasi... ;)

Demikian penjelasan ringkas proses dari formula yang digunakan... Sedangkan untuk kasus terakhir yang mas/mbak @Mardana temui, mungkin karena formulanya menggunakan konsep proses pertama dari penjelasan di atas, sedangkan saya menggunakan konsep proses yang kedua. Lihat perbedaan formulanya. Konsep pertama:

=SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$7)))*1)

Sedangkan konsep kedua:

=SUMPRODUCT(NOT(ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$7)))*1)

Terlihat perbedaan pada penggunaan fungsi NOT. Jadi coba diperiksa kembali formula yang digunakan (gunakan salah satu konsep/proses saja) atau baca kembali penjelasan tahapan proses di atas agar dapat dilacak masalahanya. Demikian yang dapat saya jelaskan, mudah-mudahan dapat membantu mas/mbak @Mardana menyusun sebuah formula atau melacak kesalahan-kesalahan formula... ;)

Satu hal lagi... dari gambar terakhir yang mas/mbak @Mardana lampirkan, rujukan untuk argumen Find In pada fungsi SEARCH tidak atau jangan berupa range (dalam hal ini, pada gambar terlihat rujukannya ke range L2:L6). Argumen tersebut harus merujuk ke salah satu sel saja, misalkan untuk provider Telkomsel, merujuk ke L2, provider Indosat merujuk ke L3 dan demikian dengan yang lainnya. Sehingga formula untuk mencari data provider Telkomsel (sel E4) adalah:

=SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$2)))*1)

Sedangkan formula untuk mencari data provider Indosat (sel F4) adalah:

=SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$3)))*1)

Demikian dengan yang lainnya... ;)

Sama-sama mas/mbak @Mardana ... maaf, saya tidak tahu harus memanggil mas atau mbak... :) Saya hanya mencoba membantu dengan apa yang saya ketahui. Semoga bermanfaat. Dan silahkan diskusikan kembali jika ada masalah atau kendala... ;)