Formula umum
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Ringkasan
Untuk menghitung nilai unik dengan satu atau beberapa kondisi, Anda bisa menggunakan rumus yang didasarkan pada UNIK dan FILTER. Pada contoh yang ditampilkan, rumus di H7 adalah:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
yang menghasilkan 3, karena ada tiga nama unik di B6: B15 yang terkait dengan proyek Omega.
Catatan: rumus ini memerlukan Rumus Array Dinamis, hanya tersedia di Excel 365. Dengan versi Excel yang lebih lama, Anda bisa menggunakan rumus alternatif yang lebih kompleks.
Penjelasan
Pada intinya, rumus ini menggunakan fungsi UNIK untuk mengekstrak nilai unik, dan fungsi FILTER menerapkan kriteria.
Bekerja dari dalam ke luar, fungsi FILTER digunakan untuk menerapkan kriteria dan hanya mengekstrak nama yang terkait dengan proyek "Omega":
FILTER(B6:B15,C6:C15=H6) // Omega names only
Hasil dari FILTER berupa larik seperti ini:
("Jim";"Jim";"Carl";"Sue";"Carl")
Selanjutnya, fungsi UNIQUE digunakan untuk menghapus duplikat:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
yang menghasilkan array baru seperti ini:
("Jim";"Carl";"Sue") // after UNIQUE
Saat ini, kami memiliki daftar nama unik yang terkait dengan Omega, dan kami hanya perlu menghitungnya. Untuk alasan yang dijelaskan di bawah, kami melakukan ini dengan fungsi LEN dan fungsi SUM. Untuk memperjelas, pertama-tama kita akan menulis ulang rumusnya untuk memasukkan daftar unik:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Fungsi LEN mendapatkan panjang setiap item dalam daftar, dan mengembalikan array dengan panjang:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Selanjutnya, kami memeriksa apakah panjangnya lebih besar dari nol:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Dan gunakan negatif ganda untuk memaksa nilai TRUE dan FALSE menjadi 1 dan 0:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Akhirnya, kami menambahkan hasil dengan fungsi SUM:
=SUM((1;1;1)) // returns 3
Array ini dikirim langsung ke fungsi COUNTA, yang mengembalikan hitungan akhir:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Perhatikan bahwa karena kami memeriksa panjang setiap item yang dikembalikan oleh UNIQUE, sel kosong atau kosong yang memenuhi kriteria akan diabaikan. Rumus ini dinamis dan akan segera dihitung ulang jika data sumber diubah.
Hitung unik dengan beberapa kriteria
Untuk menghitung nilai unik berdasarkan beberapa kriteria, dapat memperluas logika "sertakan" di dalam FILTER. Misalnya, untuk menghitung nama unik proyek Omega hanya di bulan Juni, gunakan:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Ini adalah contoh penggunaan logika boolean untuk menerapkan lebih dari satu ketentuan. Pendekatan tersebut dijelaskan lebih rinci di sini.
Untuk lebih jelasnya, lihat video pelatihan ini: Cara memfilter dengan beberapa kriteria.
COUNTA
Dimungkinkan untuk menulis rumus yang lebih sederhana yang membalas pada fungsi COUNTA. Namun peringatan penting adalah bahwa COUNTA akan mengembalikan 1 jika tidak ada nilai yang cocok. Ini karena fungsi FILTER mengembalikan kesalahan saat tidak ada data yang cocok dengan kriteria, dan kesalahan ini akhirnya dihitung oleh fungsi COUNTA. Rumus COUNTA dasar terlihat seperti ini:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Sekali lagi, rumus ini akan mengembalikan 1 jika tidak ada data yang cocok. Ini juga akan menyertakan sel kosong yang memenuhi kriteria. Formula berdasarkan LEN dan SUM adalah opsi yang lebih baik.
Tidak ada array dinamis
Jika Anda menggunakan versi Excel yang lebih lama tanpa dukungan larik dinamis, Anda dapat menggunakan rumus yang lebih kompleks. Untuk pembahasan yang lebih umum tentang alternatif larik dinamis, lihat: Alternatif untuk Rumus Array Dinamis.
