Rumus Menghitung Jumlah Data Unik Dengan Fungsi Countif Excel

 

Rumus menghitung jumlah data unik/berbeda di excel dengan mengabaikan data duplikat untuk format data teks atau angka menggunakan fungsi Countif excel

Jika memiliki data yang mengandung data yang sama (double/duplikat). Bagaimanakah rumus excel yang digunakan untuk mengetahui jumlah data uniknya? (count unique value excel). Menghitung jumlah data unik berarti mencacah banyak data, namun jika pada data tersebut memiliki duplikat hanya dihitung satu kali saja. Data unik ini bisa saja berupa teks maupun angka, juga campuran dari keduanya.

Untuk tutorial cara mendapatkan list data uniknya bisa anda pelajari pada halaman: Rumus Excel Mendapatkan Data Unik Tanpa Kembar. Salah satu cara untuk menghitung jumlah data unik dari sebuah range data pada excel adalah dengan menggunakan rumus-rumus excel berikut:

RUMUS MENGHITUNG JUMLAH DATA UNIK/BERBEDA DI EXCEL

Untuk menghitung jumlah data unik bisa menggunakan rumus gabungan fungsi Sum dan Countif seperti berikut:

=SUM(1/COUNTIF(RangeData; RangeData))

Akhiri dengan 

        Ctrl + Shift + Enter (Array Formula).

Gambar 1 : Menghitung Data Unik Dengan Fungsi SUM dan COUNTIF

Pada contoh diatas jumlah jenis buah pada range A2:A11 dihitung dengan rumus:

=SUM(1/COUNTIF(A2:A11;A2:A11))

Kemudian tekan Ctrl + Shift + Enter, sehingga rumus excel tersebut akan diapit tanda kurung ({...}) seperti yang terlihat di contoh.

Cara kerja rumus excel diatas adalah sebagai berikut:

• Fungsi Countif akan menghitung jumlah masing-masing jenis buah pada range data: Mangga = 1, Manggis = 3, Rambutan= 1, Sirsak = 1, Manggis = 3, Jeruk = 1, Apel = 2, Manggis= 3, Nanas = 1, Apel = 2
• Sehingga menghasilkan angka berikut: 

               {1;3;1;1;3;1;2;3;1;2}

• Angka diatas kemudian digunakan untuk membagi angka 1

    

             1/{1;3;1;1;3;1;2;3;1;2}

• Setelah mendapatkan angka tersebut kemudian dijumlahkan dengan fungsi SUM

        =SUM(1/1;1/3;1/1;1/1;1/3;1/1;1/2;1/3;1/1;1/2)

• Sehingga menghasilkan jumlah unik jenis buah ada 7.

Penjelasan cara kerja rumus diatas sekaligus sebagai penjelasan untuk rumus-rumus excel berikutnya pada tutorial excel kali ini.

Selain menggunakan rumus array gabungan fungsi SUM dan COUNTIF diatas, untuk rumus non Array anda bisa menggunakan gabungan fungsi SUMPRODUCT dan COUNTIF berikut:

=SUMPRODUCT(1/COUNTIF(RangeData;RangeData))

Pada contoh diatas rumus excel yang digunakan untuk menghitung jumlah jenis buah adalah:

=SUMPRODUCT(1/COUNTIF(A2:A11;A2:A11))

Cara kerja rumus diatas kurang lebih sama dengan cara kerja rumus sebelumnya.

Penjelasan lebih detail untuk masing-masing fungsi bisa anda baca pada laman berikut: Rumus SUM Excel, Rumus SUMPRODUCT Excel, Rumus COUNTIF Excel.

Jika data yang kita hitung jumlah nilai uniknya mengandung sel kosong (blank cells), ternyata kedua rumus excel diatas tidak bekerja sesuai harapan. Kedua rumus diatas akan menghasilkan pesan error #DIV/0! seperti pada gambar ini.

Rumus Menghitung Jumlah Data Unik Yang Mengandung Cell Kosong

Jika pada range data yang kita hitung nilai uniknya terdapat sel kosong (blank cells). Maka solusinya adalah dengan menambahkan (&"") pada kriteria fungsi Countif, sehingga rumus excelnya menjadi:

=SUM(1/COUNTIF(RangeData;RangeData&""))

Akhiri dengan Ctrl + Shift + Enter.

Untuk rumus non Array menjadi:

=SUMPRODUCT(1/COUNTIF(RangeData;RangeData&""))

Jika anda cermati, ternyata dengan menggunakan rumus excel diatas sel yang kosong (Blank Cells) juga ikut terhitung. Data unik pada contoh diatas seharusnya 6 (Mangga, Manggis, Rambutan, Jeruk, Apel, Nanas), namun hasil rumus diatas 7 sebab sel kosong juga ikut terhitung sehingga plus 1 menjadi 7 Jenis.

Jika anda bermaksud mengabaikan sel kosong (Blank Cells), maka gunakan rumus excel berikutnya dibawah ini.

Rumus Menghitung Jumlah Data Berbeda & Mengabaikan Cell Kosong

Rumus excel yang digunakan untuk mengetahui jumlah data unik dengan mengabaikan sel kosong (blank cells) adalah sebagai berikut:

=SUM((RangeData<>"")/COUNTIF(RangeData;RangeData&""))

Akhiri dengan Ctrl + Shift + Enter.

Untuk rumus non array bisa menggunakan gabungan Sumproduct dan Countif seperti dibawah ini:

=SUMPRODUCT((RangeData<>"")/COUNTIF(RangeData;RangeData&""))

Contoh penerapannya seperti yang terlihat dalam gambar berikut:

Seperti yang terlihat pada gambar diatas dengan menggunakan rumus array:

=SUM((A2:A11<>"")/COUNTIF(A2:A11;A2:A11&""))

Akhiri dengan Ctrl + Shift + Enter.

atau untuk non Array dengan rumus:

=SUMPRODUCT((A2:A11<>"")/COUNTIF(A2:A11;A2:A11&""))

Kita bisa menghitung jumlah nilai unik dari sebuah range data dan mengabaikan sel yang kosong (sel kosong tidak dihitung).

Untuk data yang cukup besar, menghitung jumlah data unik dengan fungsi COUNTIF diatas sebenarnya masih kurang maksimal. Untuk performance yang lebih baik bisa anda pertimbangkan untuk menggunakan fungsi FREQUENCY