Formula umum
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Ringkasan
Untuk menghitung berapa kali sebuah kata tertentu (atau substring) muncul di dalam rentang sel, Anda dapat menggunakan rumus berdasarkan fungsi SUBSTITUTE, LEN, dan SUMPRODUCT. Pada contoh yang ditampilkan, rumus di C11 adalah:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Catatan: Rumus di halaman ini menghitung keadaan sebuah kata dalam suatu rentang. Misalnya, jika sel berisi dua contoh kata, itu akan berkontribusi 2 ke jumlah total. Jika Anda hanya ingin menghitung sel yang berisi kata tertentu, lihat rumus sederhana ini berdasarkan fungsi COUNTIF.
Penjelasan
Dalam versi umum rumus, rng mewakili rentang untuk diperiksa, dan txt adalah kata atau substring untuk dihitung.
Dalam contoh yang ditampilkan, B5: B8 adalah rentang untuk diperiksa, dan C2 berisi teks (kata atau substring) untuk dihitung.
Untuk setiap sel dalam rentang tersebut, SUBSTITUTE menghapus substring dari teks asli dan LEN menghitung panjang teks tanpa substring. Jumlah ini kemudian dikurangi dari panjang teks asli. Hasilnya adalah jumlah karakter yang telah dihapus oleh SUBSTITUTE.
Kemudian, jumlah karakter yang dihapus dibagi dengan panjang substring. Jadi, jika substring atau kata terdiri dari 5 karakter, dan ada 10 karakter yang hilang setelah dihapus dari teks aslinya, kita tahu bahwa substring / kata tersebut muncul dua kali dalam teks aslinya.
Karena kalkulasi di atas dibungkus dalam fungsi SUMPRODUCT, hasilnya adalah larik yang berisi angka untuk setiap sel dalam rentang tersebut. Angka-angka ini mewakili jumlah kemunculan substring di setiap sel. Untuk contoh ini, array terlihat seperti ini: (1; 1; 0; 1)
Terakhir, SUMPRODUCT menjumlahkan semua item dalam larik untuk mendapatkan total kemunculan substring dalam rentang sel.
Mengabaikan kasus
SUBSTITUTE adalah fungsi case-sensitive, jadi akan cocok dengan case saat menjalankan substitusi. Jika Anda perlu menghitung kemunculan huruf besar dan kecil dari sebuah kata atau substring, gunakan fungsi UPPER di dalam SUBSTITUTE untuk mengonversi teks menjadi huruf besar sebelum menjalankan substitusi:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
