Formula generik
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Ringkasan
Untuk mengira baris yang sepadan dengan beberapa kriteria ATAU, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula dalam F10 adalah:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Formula ini mengembalikan bilangan baris di mana lajur satu adalah A atau B dan lajur dua adalah X, Y, atau Z.
Penjelasan
Bekerja dari dalam ke luar, setiap kriteria diterapkan dengan pembinaan ISNUMBER + MATCH yang terpisah. Untuk menghasilkan kiraan baris di lajur satu di mana nilainya adalah A atau B yang kami gunakan:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH menghasilkan susunan hasil yang kelihatan seperti ini:
(1;2;#N/A;1;2;1;2)
dan ISNUMBER menukar tatasusunan ini kepada tatasusunan ini:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Untuk menghasilkan kiraan baris di lajur dua di mana nilainya adalah X, Y, atau Z kita gunakan:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Kemudian MATCH kembali:
(1;2;3;3;#N/A;1;2)
dan ISNUMBER menukar kepada:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Dua tatasusunan ini digandakan bersama di dalam SUMPRODUCT, yang secara automatik menukar nilai TRUE FALSE menjadi 1 dan 0 sebagai sebahagian daripada operasi matematik.
Jadi, untuk menggambarkan, hasil akhir dihasilkan 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 rujukan sel
Contoh di atas menggunakan pemalar susunan berkod keras, tetapi anda juga boleh menggunakan rujukan 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 cabaran formula ini.
