Formula umum
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Ringkasan
Untuk menghitung sel yang berisi x atau y, Anda bisa menggunakan rumus berdasarkan fungsi SUMPRODUCT. Dalam contoh yang diperlihatkan, rumus di sel F5 adalah:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Ini adalah solusi rumus sel tunggal, yang dijelaskan di bawah. Juga dimungkinkan untuk menggunakan rumus yang lebih sederhana berdasarkan kolom pembantu, juga dijelaskan di bawah ini.
Penjelasan
Saat Anda menghitung sel dengan "logika OR", Anda harus berhati-hati agar tidak menghitung dua kali lipat. Misalnya, jika Anda menghitung sel yang berisi "abc" atau "def", Anda tidak bisa hanya menambahkan dua fungsi COUNTIF, karena Anda dapat menghitung sel ganda yang berisi "abc" dan "def".
Solusi sel tunggal
Untuk satu formula, Anda dapat menggunakan SUMPRODUCT dengan ISNUMBER + FIND. Rumus di F5 adalah:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Rumus ini didasarkan pada rumus yang dijelaskan di sini yang menempatkan teks di dalam sel:
ISNUMBER(FIND("abc",B5:B11)
Saat diberi rentang sel, cuplikan ini akan mengembalikan larik nilai TRUE / FALSE, satu nilai untuk setiap sel rentang tersebut. Karena kita menggunakan ini dua kali (sekali untuk "abc" dan sekali untuk "def"), kita akan mendapatkan dua array.
Selanjutnya, kami menambahkan larik ini bersama-sama (dengan +), yang membuat satu larik angka baru. Setiap angka dalam larik ini adalah hasil dari penambahan nilai TRUE dan FALSE dalam dua larik asli secara bersamaan. Dalam contoh yang ditunjukkan, larik yang dihasilkan terlihat seperti ini:
(2;0;2;0;1;0;2)
Kita perlu menjumlahkan angka-angka ini, tetapi kita tidak ingin menghitung dua kali lipat. Jadi kita perlu memastikan nilai apa pun yang lebih besar dari nol hanya dihitung sekali. Untuk melakukan itu, kita memaksa semua nilai menjadi TRUE atau FALSE dengan "> 0", lalu memaksa ke 1/0 dengan negatif ganda (-).
Terakhir, SUMPRODUCT mengembalikan jumlah semua nilai dalam larik.
Solusi kolom pembantu
Dengan kolom pembantu untuk memeriksa setiap sel satu per satu, masalahnya tidak terlalu rumit. Kita dapat menggunakan COUNTIF dengan dua nilai (disediakan sebagai "konstanta array"). Rumus di C5 adalah:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF akan mengembalikan larik yang berisi dua item: hitungan untuk "abc" dan hitungan untuk "def". Untuk mencegah penghitungan ganda, kami menambahkan item ke atas dan kemudian memaksa hasilnya menjadi TRUE / FALSE dengan "> 0". Terakhir, kami mengonversi nilai TRUE / FALSE menjadi 1 dan 0 dengan negatif ganda (-).
Hasil akhirnya adalah 1 atau 0 untuk setiap sel. Untuk mendapatkan total semua sel dalam rentang tersebut, cukup jumlahkan kolom pembantu.
