Formula umum
SUMPRODUCT(--(A:A=A1))
Ringkasan
Kata pengantar
Ini adalah pengantar yang sangat panjang, tetapi konteksnya penting, maaf!
Jika Anda mencoba menghitung angka yang sangat panjang (16+ digit) dalam rentang dengan COUNTIF, Anda mungkin melihat hasil yang salah, karena bug dalam cara fungsi tertentu menangani angka panjang, bahkan ketika angka tersebut disimpan sebagai teks. Perhatikan layar di bawah ini. Semua hitungan di kolom D salah -meskipun setiap angka di kolom B unik, hitungan yang dikembalikan oleh COUNTIF menunjukkan bahwa angka-angka ini adalah duplikat.
=COUNTIF(data,B5)
Masalah ini terkait dengan cara Excel menangani angka. Excel hanya dapat menangani 15 digit signifikan, dan jika Anda memasukkan angka dengan lebih dari 15 digit di Excel, Anda akan melihat digit tambahan diam-diam diubah menjadi nol. Masalah penghitungan yang disebutkan di atas muncul dari batas ini.
Biasanya, Anda dapat menghindari batas ini dengan memasukkan angka yang panjang sebagai teks, baik dengan memulai angka dengan kutipan tunggal ('999999999999999999) atau dengan memformat sel sebagai Teks sebelum memasukkan. Selama Anda tidak perlu melakukan operasi matematika pada angka, ini adalah solusi yang baik, dan ini memungkinkan Anda memasukkan angka ekstra panjang untuk hal-hal seperti nomor kartu kredit dan nomor seri tanpa kehilangan angka apa pun.
Namun, jika Anda mencoba menggunakan COUNTIF untuk menghitung angka dengan lebih dari 15 digit (bahkan saat disimpan sebagai teks), Anda mungkin melihat hasil yang tidak dapat diandalkan. Ini terjadi karena COUNTIF secara internal mengonversi nilai panjang kembali ke angka di beberapa titik selama pemrosesan, memicu batas 15 digit yang dijelaskan di atas. Tanpa semua digit, beberapa angka dapat dihitung seperti duplikat jika dihitung dengan COUNTIF.
Larutan
Salah satu solusinya adalah mengganti rumus COUNTIF dengan rumus yang menggunakan SUM atau SUMPRODUCT. Dalam contoh yang ditunjukkan, rumus dalam E5 terlihat seperti ini:
=SUMPRODUCT(--(data=B5))
Rumusnya menggunakan rentang bernama "data" (B5: B9) dan menghasilkan jumlah yang benar untuk setiap nomor dengan SUMPRODUCT.
Penjelasan
Pertama, ekspresi di dalam SUMPRODUCT membandingkan semua nilai dalam rentang bernama "data" dengan nilai dari kolom B di baris saat ini. Ini menghasilkan larik hasil BENAR / SALAH.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Selanjutnya, negatif ganda memaksa nilai TRUE / FALSE menjadi nilai 1/0.
=SUMPRODUCT((1;0;0;0;0))
Terakhir, SUMPRODUCT hanya menjumlahkan item dalam larik dan mengembalikan hasilnya.
Varian rumus array
Anda juga dapat menggunakan fungsi SUM sebagai ganti SUMPRODUCT, tetapi ini adalah rumus array dan harus dimasukkan dengan control + shift + enter:
(=SUM(--(B:B=B5)))
Fungsi lain dengan masalah ini
Saya belum memverifikasi ini sendiri, tetapi tampaknya beberapa fungsi memiliki masalah yang sama, termasuk SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, dan AVERAGEIFS.
