Formula generik
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Ringkasan
Untuk mengira dengan pelbagai kriteria, termasuk logik untuk TIDAK salah satu daripada beberapa perkara, anda boleh menggunakan fungsi SUMPRODUCT bersama dengan fungsi MATCH dan ISNA.
Dalam contoh yang ditunjukkan, formula dalam G8 adalah:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Di mana "jantina" adalah julat bernama C4: C12, dan "kumpulan" adalah julat bernama D4: D12.
Catatan: PERTANDINGAN dan ISNA membenarkan formula untuk skala dengan mudah untuk menangani lebih banyak pengecualian, kerana anda dapat memperluas julat dengan mudah untuk memasukkan nilai "TIDAK" tambahan.
Penjelasan
Ungkapan pertama di dalam SUMPRODUCTS menguji nilai di lajur C, Jantina, terhadap nilai di F4, "Lelaki":
(gender=F4)
Hasilnya adalah pelbagai nilai BENAR BENAR seperti ini:
(BENAR; SALAH; BENAR; SALAH; BENAR; BENAR; SALAH; BENAR; SALAH)
Di mana BENAR sepadan dengan "Lelaki".
Ungkapan kedua di dalam SUMPRODUCTS menguji nilai di lajur D, Kumpulan, terhadap nilai di G4: G5, "A" dan "B". Ujian ini dikendalikan dengan MATCH dan ISNA seperti ini:
ISNA(MATCH(group,G4:G5,0))
Fungsi MATCH digunakan untuk mencocokkan setiap nilai dalam julat bernama "group" dengan nilai di G4: G5, "A" dan "B". Sekiranya perlawanan berjaya, MATCH mengembalikan nombor. Apabila MATCH gagal, MATCH mengembalikan # N / A. Hasilnya adalah susunan seperti ini:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Oleh kerana nilai # N / A sesuai dengan "not A atau B", ISNA digunakan untuk "membalikkan" array ke:
(SALAH; SALAH; BENAR; SALAH; SALAH; BENAR; SALAH; SALAH; BENAR)
Sekarang BENAR sesuai dengan "bukan A atau B".
Di dalam SUMPRODUCT, dua hasil susunan digandakan bersama, yang menghasilkan susunan angka tunggal di dalam SUMPRODUCT:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
SUMPRODUCT kemudian mengembalikan jumlah, 2, mewakili "2 lelaki yang tidak berada dalam kumpulan A atau B".
