Formula umum
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Ringkasan
Untuk menghitung baris yang cocok dengan beberapa kriteria ATAU, Anda dapat menggunakan rumus berdasarkan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, rumus di F10 adalah:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Rumus ini mengembalikan hitungan baris di mana kolom satu adalah A atau B dan kolom dua adalah X, Y, atau Z.
Penjelasan
Bekerja dari dalam ke luar, setiap kriteria diterapkan dengan konstruksi ISNUMBER + MATCH terpisah. Untuk menghasilkan hitungan baris di kolom satu di mana nilainya adalah A atau B kami menggunakan:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH menghasilkan larik hasil yang terlihat seperti ini:
(1;2;#N/A;1;2;1;2)
dan ISNUMBER mengubah larik ini menjadi larik ini:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Untuk menghasilkan hitungan baris di kolom dua di mana nilainya adalah X, Y, atau Z kami menggunakan:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Kemudian MATCH kembali:
(1;2;3;3;#N/A;1;2)
dan ISNUMBER diubah menjadi:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Kedua larik ini dikalikan bersama di dalam SUMPRODUCT, yang secara otomatis mengonversi nilai TRUE FALSE menjadi 1 dan 0 sebagai bagian dari operasi matematika.
Jadi, untuk memvisualisasikan, hasil akhirnya diturunkan seperti ini:
=SUMPRODUCT((1;1;0;1;1;1;1)*(1;1;1;1;0;1;1)) =SUMPRODUCT((1;1;0;1;0;1;1)) =5
Dengan referensi sel
Contoh di atas menggunakan konstanta array yang di-hardcode, tetapi Anda juga bisa menggunakan referensi sel:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Lebih banyak kriteria
Pendekatan ini dapat "ditingkatkan" untuk menangani lebih banyak kriteria. Anda dapat melihat contoh dalam tantangan rumus ini.
