Formula umum
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Ringkasan
Untuk menghitung nilai dalam satu daftar yang hilang dari daftar lain, Anda bisa menggunakan rumus yang didasarkan pada fungsi COUNTIF dan SUMPRODUCT.
Pada contoh yang ditampilkan, rumus di H6 adalah:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Yang mengembalikan 1 karena nilai "Osborne" tidak muncul di B6: B11.
Penjelasan
Fungsi COUNTIF memeriksa nilai dalam rentang terhadap kriteria. Seringkali, hanya satu kriteria yang diberikan, tetapi dalam hal ini kami menyediakan lebih dari satu kriteria.
Untuk rentang, kami memberikan COUNTIF daftar rentang bernama1 (B6: B11), dan untuk kriteria, kami menyediakan daftar rentang bernama2 (F6: F8).
Karena kami memberikan COUNTIF lebih dari satu kriteria, kami mendapatkan lebih dari satu hasil dalam larik hasil yang terlihat seperti ini: (2; 1; 0)
Kami hanya ingin menghitung nilai yang hilang, yang menurut definisi memiliki jumlah nol, jadi kami mengonversi nilai ini menjadi TRUE dan FALSE dengan pernyataan "= 0", yang menghasilkan: (FALSE; FALSE; TRUE)
Kemudian kita paksakan nilai TRUE FALSE menjadi 1s dan 0s dengan operator double-negative (-), yang menghasilkan: (0; 0; 1)
Terakhir, kami menggunakan SUMPRODUCT untuk menjumlahkan item dalam larik dan mengembalikan jumlah total nilai yang hilang.
Alternatif dengan MATCH
Jika Anda lebih suka rumus yang lebih literal, Anda dapat menggunakan rumus di bawah ini, berdasarkan MATCH, yang secara harfiah menghitung nilai yang "hilang" menggunakan fungsi ISNA:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
