Formula umum
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
Ringkasan
Untuk menghitung jumlah kata atau kata kunci tertentu yang muncul di sel tertentu, Anda bisa menggunakan rumus berdasarkan fungsi SEARCH, ISNUMBER, dan SUMPRODUCT. Pada contoh yang ditunjukkan, rumus di C5 adalah:
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B5)))
di mana "kata kunci" adalah rentang bernama E5: E9.
Penjelasan
Catatan: jika kata kunci muncul lebih dari sekali dalam sel tertentu, itu hanya akan dihitung sekali. Dengan kata lain, rumus hanya menghitung contoh kata kunci yang berbeda.
Inti dari rumus ini adalah pendekatan ISNUMBER + SEARCH untuk menemukan teks dalam sel, yang dijelaskan lebih rinci di sini. Dalam kasus ini, kami mencari di setiap sel untuk semua kata dalam rentang bernama "kata kunci" (E5: E9). Kami melakukan ini dengan meneruskan rentang ke SEARCH sebagai argumen find_text. Karena kami mengirimkan array 5 item:
("green";"orange";"white";"blue";"pink")
kami mendapatkan array 5 item kembali sebagai hasilnya:
(#VALUE!;#VALUE!;1;#VALUE!;14)
Angka sesuai dengan pertandingan, dan #VALUE! kesalahan berarti tidak ditemukan kecocokan. Dalam kasus ini, karena kami tidak peduli di mana teks ditemukan dalam sel, kami menggunakan ISNUMBER untuk mengonversi larik menjadi nilai TRUE dan FALSE:
(FALSE;FALSE;TRUE;FALSE;TRUE)
Dan negatif ganda (-) untuk mengubahnya menjadi 1 dan nol:
(0;0;1;0;1)
Fungsi SUMPRODUCT kemudian mengembalikan jumlah array, 2 dalam kasus ini.
Menangani kata kunci kosong
Jika rentang kata kunci berisi sel kosong, rumus tidak akan berfungsi dengan benar, karena fungsi SEARCH mengembalikan nol saat mencari string kosong (""). Untuk memfilter sel kosong mana pun dalam rentang kata kunci, Anda dapat menggunakan variasi di bawah ini:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Catatan: versi ini rumus array dan harus dimasukkan dengan control + shift + enter.
