Formula umum
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Ringkasan
Untuk menjumlahkan jika sel berisi salah satu string teks atau lainnya (yaitu berisi "kucing" atau "tikus") Anda dapat menggunakan fungsi SUMPRODUCT bersama dengan ISNUMBER + SEARCH atau FIND. Dalam contoh yang diperlihatkan, rumus di sel F5 adalah:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
yang mengembalikan jumlah nilai di C4: C8 saat sel di B4: B8 berisi "kucing" atau "tikus".
Penjelasan
Saat Anda menjumlahkan sel dengan kriteria "ATAU", Anda harus berhati-hati agar tidak menghitung dua kali lipat jika ada kemungkinan bahwa kedua kriteria akan mengembalikan nilai benar. Dalam contoh yang ditampilkan, kami ingin menjumlahkan nilai di Kolom C saat sel di kolom B berisi "kucing" atau "tikus". Kami tidak dapat menggunakan SUMIF dengan dua kriteria, karena SUMIFS didasarkan pada logika DAN. Dan jika kita mencoba menggunakan dua SUMIFS (yaitu SUMIFS + SUMIFS) kita akan menghitung dua kali lipat karena ada sel yang mengandung "kucing" dan "tikus".
Sebagai gantinya, kami menggunakan rumus seperti ini:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Inti dari rumus ini didasarkan pada rumus yang dijelaskan di sini yang menempatkan teks di dalam sel dengan ISNUMBER dan SEARCH:
ISNUMBER(SEARCH("text",range)
Saat diberi rentang sel, cuplikan ini akan mengembalikan larik nilai TRUE / FALSE, satu nilai untuk setiap sel rentang tersebut. Dalam rumus ini, kita menggunakan potongan ini dua kali, sekali untuk "kucing" dan sekali untuk "tikus", jadi kita akan mendapatkan dua larik. Saat ini, kami memiliki:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Selanjutnya, kami menambahkan array ini bersama-sama karena penambahan digunakan dalam aljabar boolean untuk logika OR. Operasi matematika secara otomatis memaksa nilai TRUE dan FALSE menjadi 1 dan 0, jadi kami berakhir dengan array di bawah ini:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Setiap angka dalam array ini adalah hasil dari penambahan nilai TRUE dan FALSE dalam dua array asli secara bersamaan. Dalam contoh yang ditunjukkan, array terlihat seperti ini:
(2;0;2;1;0)
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 memeriksa array dengan "> 0". Ini mengembalikan TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Yang kemudian kita ubah menjadi 1/0 menggunakan negatif ganda (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
dan akhirnya:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT mengalikan elemen yang sesuai dari dua larik bersama-sama dan menjumlahkan hasilnya, menghasilkan 70.
Opsi peka huruf besar-kecil
Fungsi SEARCH mengabaikan kapitalisasi. Jika Anda membutuhkan opsi sensitif, ganti SEARCH dengan fungsi FIND.
