Formula generik
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Ringkasan
Untuk mengira nilai dalam satu senarai yang hilang dari senarai lain, anda boleh menggunakan formula berdasarkan fungsi COUNTIF dan SUMPRODUCT.
Dalam contoh yang ditunjukkan, formula dalam H6 adalah:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Yang mengembalikan 1 kerana nilai "Osborne" tidak muncul dalam B6: B11.
Penjelasan
Fungsi COUNTIF memeriksa nilai dalam julat dengan kriteria. Selalunya, hanya satu kriteria yang dibekalkan, tetapi dalam kes ini kita memberikan lebih dari satu kriteria.
Untuk pelbagai, kami memberi COUNTIF julat yang dinamakan Senarai1 (B6: B11), dan untuk kriteria, kami menyediakan yang dinamakan pelbagai list2 (F6: F8).
Kerana kami memberikan COUNTIF lebih dari satu kriteria, kami mendapat lebih dari satu hasil dalam susunan hasil yang kelihatan seperti ini: (2; 1; 0)
Kami ingin menghitung hanya nilai yang hilang, yang secara definisi mempunyai jumlah sifar, jadi kami menukar nilai ini menjadi BENAR dan SALAH dengan pernyataan "= 0", yang menghasilkan: (FALSE; FALSE; TRUE)
Kemudian kami memaksa nilai TRUE FALSE ke 1s dan 0s dengan operator negatif ganda (-), yang menghasilkan: (0; 0; 1)
Akhirnya, kami menggunakan SUMPRODUCT untuk menambahkan item dalam array dan mengembalikan jumlah nilai yang hilang.
Alternatif dengan PERTANDINGAN
Sekiranya anda lebih suka formula yang lebih literal, anda boleh menggunakan formula di bawah ini, berdasarkan MATCH, yang secara harfiah mengira nilai yang "hilang" menggunakan fungsi ISNA:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
