Formula umum
=SUMPRODUCT(--NOT(ISERROR(range)))
Ringkasan
Untuk menghitung jumlah sel yang tidak mengandung kesalahan, Anda dapat menggunakan fungsi ISERROR dan NOT, yang dibungkus dalam fungsi SUMPRODUCT. Dalam contoh yang ditampilkan, rumus dalam E5 adalah:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Penjelasan
Dalam contoh ini, tujuannya adalah menghitung jumlah sel dalam rentang yang tidak mengandung kesalahan.
Bekerja dari dalam ke luar, pertama-tama kami menggunakan fungsi ISERROR di seluruh rentang:
ISERROR(B5:B14) // check all 10 cells
Karena ada sepuluh sel dalam rentang B5: B14, ISERROR mengembalikan larik dengan sepuluh hasil seperti ini:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Di sini, setiap nilai TRUE menunjukkan nilai sel yang merupakan kesalahan. Karena tujuannya adalah untuk menghitung sel yang tidak mengandung kesalahan, kami membalikkan hasil ini dengan fungsi NOT:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
yang mengembalikan:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Perhatikan bahwa setiap nilai TRUE sekarang sesuai dengan sel yang tidak mengandung kesalahan. Array ini sekarang dalam format yang benar - nilai TRUE menunjukkan sel tanpa kesalahan, nilai FALSE menunjukkan sel dengan kesalahan.
Karena SUMPRODUCT hanya bekerja dengan data numerik, langkah selanjutnya adalah mengonversi nilai TRUE dan FALSE menjadi ekuivalen numeriknya, 1 dan 0. Kami melakukan ini dengan negatif ganda (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Array yang dihasilkan terlihat seperti ini:
(1;0;1;1;1;0;1;1;1;0)
Terakhir, SUMPRODUCT menjumlahkan item dalam larik ini dan mengembalikan total, yang dalam contoh adalah angka 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Fungsi ISERR
Seperti fungsi ISERROR, fungsi ISERR mengembalikan TRUE jika nilainya adalah kesalahan. Perbedaannya adalah bahwa ISERR mengabaikan kesalahan # N / A. Jika Anda ingin menghitung sel yang tidak mengandung kesalahan, dan mengabaikan kesalahan # N / A, Anda dapat mengganti ISERR untuk ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
Opsi SUM
Anda juga dapat menggunakan fungsi SUM untuk menghitung kesalahan. Struktur rumusnya sama:
=SUM(--NOT(ISERROR(B5:B14)))
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
