Cara Menghitung Sel Tidak Kosong di Excel: 5 Metode Utama

Saya juga bisa menghitung melintasi baris untuk melihat seberapa lengkap respons setiap orang:

=COUNTA(B2:F2)Didukung oleh

Untuk Sara Ibrahim, ini mengembalikan 5 karena ia mengisi semua kolom. Untuk Nour Mahmoud, =COUNTA(B6:F6) hanya mengembalikan 1 karena ia hanya memberikan emailnya dan meninggalkan sisanya.

Kapan COUNTA() sudah cukup

Gunakan COUNTA() saat Anda memerlukan hitungan sederhana dan data Anda bersih. Jika setiap sel kosong dalam rentang Anda benar-benar kosong (tidak pernah disentuh, tanpa rumus), COUNTA() bekerja dengan sempurna.

Hal menjadi rumit saat rumus menghasilkan "" (string kosong). Saya akan menjelaskan kasus tepi itu secara detail nanti di bagian "Bagaimana Excel Memperlakukan Sel Kosong vs. Hampa".

Menghitung Sel Tidak Kosong dengan COUNTIF()

COUNTIF() memberi Anda cara lain untuk menghitung sel tidak kosong. Alih-alih menghitung semua yang ada, fungsi ini menghitung sel yang memenuhi suatu kondisi. Dan kondisinya bisa berupa "tidak kosong".

Kriteria "<>"

Berikut bentuk rumusnya:

=COUNTIF(B2:B13,"<>")Didukung oleh

Bagian "<>" berarti "tidak sama dengan tidak ada", yang merupakan cara lain untuk mengatakan "tidak kosong". Untuk kolom email yang sama seperti sebelumnya, ini memberi saya 10, sama seperti COUNTA().

Mengapa tidak menggunakan COUNTIF(range,"*")?

Saya sering melihat ini di tutorial lama, dan ini adalah kesalahan. Wildcard "*" hanya menghitung teks. Ini sepenuhnya mengabaikan angka, tanggal, dan nilai TRUE/FALSE. Jika kolom Anda memiliki tipe data campuran, Anda akan mendapatkan hitungan yang salah.

Tetap gunakan "<>" saja. Ini berfungsi apa pun jenis data Anda.

Kapan COUNTIF() lebih baik daripada COUNTA()

Kelebihan utama COUNTIF() adalah ia mengarah secara alami ke COUNTIFS(). Begitu Anda perlu menghitung sel tidak kosong yang juga memenuhi kondisi lain (seperti departemen tertentu atau rentang tanggal), COUNTIFS() siap digunakan. Jika Anda pikir mungkin akan menambahkan kondisi nanti, mulai dengan COUNTIF() agar peralihannya mudah.

Untuk sebagian besar data, COUNTA() dan COUNTIF(range,"<>") memberikan hasil identik. Keduanya menghitung hal yang sama, termasuk error dan blanko dari rumus. Perbedaan utamanya adalah fleksibilitas. COUNTIF() adalah jembatan Anda ke penghitungan multi-kriteria dengan COUNTIFS().

Menghitung Sel Tidak Kosong yang Memenuhi Kondisi

Di sinilah COUNTIF() benar-benar unggul. COUNTIFS() memungkinkan Anda menghitung sel tidak kosong yang juga memenuhi kondisi lain.

COUNTIFS() dengan logika tidak kosong

Misalnya saya melacak pelatihan karyawan dan ingin tahu berapa banyak orang di Sales yang telah mengumpulkan skor. Berikut rumusnya:

=COUNTIFS(B2:B11,"Sales",E2:E11,"<>")Didukung oleh

Artinya: "Hitung baris di mana Department adalah 'Sales' DAN Score tidak kosong." Di pelacak saya, ini menghasilkan 3 karena Mona Sayed berada di Sales tetapi belum menyerahkan skor.

Banyak kondisi

Anda dapat menumpuk hingga 127 pasangan kriteria jika perlu. Berikut contoh yang menghitung karyawan bertanda "Complete" yang juga memiliki skor:

=COUNTIFS(C2:C11,"Complete",E2:E11,"<>")Didukung oleh

Ini menghasilkan 6 karena Mona Sayed menyelesaikan pelatihannya tetapi tidak pernah mengumpulkan skor.

Contoh lain. Menghitung orang Engineering yang memiliki tanggal penyelesaian:

=COUNTIFS(B2:B11,"Engineering",D2:D11,"<>")Didukung oleh

Ini mengembalikan 3 karena pelatihan Omar Mostafa masih tertunda dan belum memiliki tanggal.

Polanya sederhana: gunakan "<>" untuk kolom yang Anda periksa nilai tidak kosongnya, dan letakkan filter spesifik Anda di kolom lainnya.

Bagaimana Excel Memperlakukan Sel "Kosong" vs. "Hampa"

Di sinilah sebagian besar kesalahan penghitungan terjadi. Excel melihat berbagai tipe "kosong" yang semuanya terlihat sama di layar.

Sel benar-benar kosong

Sel yang benar-benar kosong tidak pernah disentuh atau telah dibersihkan sepenuhnya. ISBLANK() mengembalikan TRUE, COUNTA() melewatinya, dan COUNTBLANK() menghitungnya. Ini adalah satu-satunya tipe sel yang disepakati semua fungsi sebagai kosong.

String kosong yang dihasilkan rumus

Ini sering menjebak orang. Saat rumus seperti =IF(A1>60,"Pass","") menghasilkan "", sel tampak kosong tetapi sebenarnya berisi hasil rumus. Berikut cara fungsi berbeda melihatnya:

• COUNTA() → Menghitungnya (tidak kosong)
• COUNTIF(range,"<>") → Menghitungnya (tidak kosong)
• COUNTBLANK() → Menghitungnya (kosong)
• ISBLANK() → Mengembalikan FALSE

Lihat masalahnya? COUNTBLANK() mengatakan kosong, tetapi ISBLANK() mengatakan tidak. Ini bisa menjebak bahkan pengguna Excel berpengalaman.

Solusi alternatif SUMPRODUCT() + LEN()

Untuk menghitung hanya sel dengan konten yang benar-benar terlihat (bukan blanko dari rumus, bukan spasi), gunakan ini:

=SUMPRODUCT(--(LEN(B5:B10)>0))Didukung oleh

Ini memeriksa berapa banyak karakter di setiap sel. Jika LEN() mengembalikan 0, sel kosong apa pun penyebabnya. Bagian -- mengonversi TRUE/FALSE menjadi 1/0, dan SUMPRODUCT()` menjumlahkannya.

Untuk hitungan yang lebih ketat yang juga mengabaikan sel dengan spasi:

=SUMPRODUCT(--(LEN(TRIM(B5:B10))>0))Didukung oleh

Sel dengan karakter tersembunyi

Kadang sel tampak kosong tetapi memiliki spasi (CHAR(32)) atau spasi tak putus (CHAR(160)) dari data web atau PDF. COUNTA() menghitungnya, LEN() mengembalikan 1 atau lebih, dan ISBLANK() mengatakan FALSE.

Untuk menangkap ini dalam hitungan Anda, gunakan rumus yang lebih ketat yang saya tunjukkan sebelumnya: =SUMPRODUCT(--(LEN(TRIM(range))>0)). Bagian TRIM() menghapus spasi biasa sebelum menghitung. Untuk spasi tak putus, bersihkan data Anda terlebih dahulu dengan =SUBSTITUTE(A1,CHAR(160),"") atau gunakan fungsi CLEAN() .

Menghitung Sel Tidak Kosong dalam Data Terfilter

Ada hal yang sering mengejutkan: Anda memfilter data, menulis rumus COUNTA(), dan hasilnya tetap mencakup baris tersembunyi. Itu karena COUNTA() tidak peduli dengan filter. Ia menghitung semuanya di rentang, terlihat atau tidak.

SUBTOTAL(): Alternatif yang peka filter

SUBTOTAL() memperbaiki ini. Fungsi ini menghitung dengan menghormati filter.

=SUBTOTAL(3,D2:D11)Didukung oleh

Angka 3 memberi tahu SUBTOTAL() untuk melakukan hitungan bergaya COUNTA(). Bedanya: fungsi ini melewati baris yang disembunyikan oleh filter.

Nomor fungsi: 3 vs. 103

SUBTOTAL() memiliki dua mode:

• Nomor fungsi 3: Melewati baris yang disembunyikan oleh AutoFilter, tetapi tetap menghitung baris yang disembunyikan secara manual.
• Nomor fungsi 103: Melewati SEMUA baris tersembunyi (baik karena filter maupun manual).

=SUBTOTAL(3,D2:D11)    -- Melewati baris AutoFilter
=SUBTOTAL(103,D2:D11)  -- Melewati SEMUA baris tersembunyiDidukung oleh

Sebagian besar waktu, 3 adalah yang Anda perlukan. Gunakan 103 hanya jika Anda menyembunyikan baris secara manual (klik kanan > Hide) dan ingin itu dikecualikan juga.

AGGREGATE(): Alternatif untuk data berantakan

Jika data terfilter Anda memiliki nilai error di dalamnya, SUBTOTAL() akan menghasilkan error. AGGREGATE() menanganinya dengan lebih baik:

=AGGREGATE(3,7,D2:D11)Didukung oleh

Angka 3 adalah nomor fungsi (COUNTA()), dan 7 memberi tahu untuk mengabaikan baris tersembunyi dan error. Ini adalah opsi teraman untuk data berantakan, tetapi kebanyakan orang tidak membutuhkannya.

Menghitung Sel Tidak Kosong di Excel Table

Jika data Anda berada dalam Excel Table (tekan Insert > Table atau tekan Ctrl+T), Anda dapat menggunakan referensi terstruktur alih-alih alamat sel. Ini membuat rumus Anda lebih jelas dan otomatis memperbarui saat tabel bertambah.

Referensi terstruktur dengan COUNTA()

Alih-alih menulis =COUNTA(C2:C9), Anda menulis:

=COUNTA(TaskTracker[Assigned To])Didukung oleh

Ini menghitung sel tidak kosong di kolom Assigned To pada tabel TaskTracker saya. Referensi ini otomatis melewati baris header dan meluas saat saya menambahkan baris baru.

Menggabungkan COUNTIFS() dengan referensi terstruktur

COUNTIFS() berjalan sangat baik dengan tabel:

=COUNTIFS(TaskTracker[Status],"Done",TaskTracker[Completion Date],"<>")Didukung oleh

Ini menghitung tugas yang ditandai "Done" dan memiliki tanggal penyelesaian. Di pelacak saya, ini memberikan 4 karena semua tugas yang selesai memiliki tanggal.

Mengapa tabel lebih baik untuk menghitung

Ada dua hal yang membuat tabel berguna. Pertama, referensi terstruktur otomatis melewati baris header, jadi Anda tidak perlu khawatir mulai dari baris 2. Kedua, saat Anda menambahkan baris baru ke tabel, setiap rumus yang menggunakan referensi terstruktur akan menangkap data baru tersebut. Tidak perlu memperbarui rentang secara manual.

Ini sangat cocok untuk data yang bertambah seiring waktu, seperti log, pelacak, atau data yang Anda impor secara rutin.

Masalah Umum Saat Menghitung Sel Tidak Kosong

Hitungan lebih tinggi dari perkiraan

Yang terjadi: COUNTA() menunjukkan 6, tetapi Anda hanya melihat 4 sel berisi data.

Penyebab: Dua sel memiliki rumus yang menghasilkan "". Seperti disebutkan sebelumnya, ini tampak kosong tetapi secara teknis tidak.

Solusi: Gunakan =SUMPRODUCT(--(LEN(range)>0)) alih-alih COUNTA(). Ini hanya menghitung sel dengan konten yang terlihat, mengabaikan blanko yang dihasilkan rumus.

Spasi tersembunyi mengacaukan hitungan

Yang terjadi: Sebuah sel tampak kosong, tetapi COUNTA() menghitungnya dan ISBLANK() mengatakan FALSE.

Penyebab: Sel memiliki spasi, kemungkinan dari menyalin data dari situs web atau file lain.

Solusi: Periksa dengan =LEN(A1). Jika mengembalikan lebih dari 0 saat sel tampak kosong, berarti ada karakter tersembunyi. Bersihkan data Anda terlebih dahulu dengan =TRIM(A1), atau gunakan rumus yang lebih ketat yang saya tunjukkan sebelumnya: =SUMPRODUCT(--(LEN(TRIM(range))>0)).

Nilai error ikut terhitung padahal Anda tidak menginginkannya

Yang terjadi: Anda ingin menghitung sel tidak kosong tetapi error seperti #DIV/0! atau #N/A ikut terhitung.

Penyebab: Baik COUNTA() maupun COUNTIF(range,"<>") memperlakukan error sebagai tidak kosong.

Solusi: Jika data Anda juga memiliki filter, gunakan =AGGREGATE(3,7,range) seperti yang saya sebutkan di bagian data terfilter. Angka 7 melewati baris tersembunyi dan error. Untuk data tanpa filter, gunakan =SUMPRODUCT(--(LEN(IFERROR(range,""))>0)).

Hitungan tidak berubah setelah difilter

Yang terjadi: Anda memfilter data dan COUNTA() masih menampilkan hitungan yang tidak terfilter.

Penyebab: COUNTA() benar-benar mengabaikan filter.

Solusi: Gunakan =SUBTOTAL(3,range) seperti yang dibahas di bagian data terfilter. Fungsi ini otomatis menyesuaikan berdasarkan apa yang terlihat.

Praktik Terbaik untuk Menghitung Sel Tidak Kosong

Setelah berkali-kali menghadapi masalah ini, berikut yang sekarang saya lakukan:

• Mulai dengan COUNTA() untuk dataset yang bersih dan sederhana. Ini cepat dan lugas.

• Gunakan COUNTIF(range,"<>") jika Anda mungkin menambahkan kondisi nanti. Fungsi ini terhubung alami ke COUNTIFS() untuk penghitungan multi-kriteria.

• Uji blanko dari rumus sebelum mempercayai hitungan. Cek beberapa sel "kosong" dengan =LEN(cell). Jika hasilnya bukan 0, beralihlah ke pendekatan SUMPRODUCT()+LEN().

• Gunakan SUBTOTAL(3,range) untuk data terfilter. Ini satu-satunya fungsi penghitungan yang menghormati filter.

• Bersihkan data impor terlebih dahulu. Jalankan TRIM() dan periksa CHAR(160) pada data dari situs web atau sumber lain.

• Gunakan Excel Table untuk dataset yang bertumbuh. Referensi terstruktur otomatis memperbarui saat Anda menambahkan baris.

• Dokumentasikan rumus kompleks. Jika Anda menggunakan SUMPRODUCT(--(LEN(TRIM(range))>0)), tambahkan komentar yang menjelaskan alasannya. Anda di masa depan akan berterima kasih.

Kesimpulan

Excel memberi Anda beberapa cara untuk menghitung sel tidak kosong, dan yang tepat bergantung pada situasi Anda. COUNTA() bekerja untuk sebagian besar kasus. COUNTIF() dan COUNTIFS() memungkinkan Anda menambahkan kondisi. SUBTOTAL() menghormati filter. Dan SUMPRODUCT() dengan LEN() adalah cadangan Anda saat rumus membuat blanko tak terlihat.

Triknya bukan menghafal rumus. Ini tentang memahami apa yang dianggap Excel sebagai "kosong". Rumus yang mengembalikan "" tampak kosong tetapi bukan. Karakter spasi tidak terlihat namun tetap terhitung. Setelah Anda memahami perbedaan ini, Anda tidak akan lagi terkejut oleh hitungan yang salah.

Jika Anda ingin terus membangun keterampilan Excel, lihat kursus Data Analysis in Excel kami. Kursus ini mencakup rumus, pivot table, dan pembersihan data yang selaras dengan semua yang kita bahas di sini.