Tetangga saya buka toko bahan makanan segar di pasar. Beliau tahu saya cukup sering berurusan dengan komputer, jadi suatu hari beliau bertanya ke saya sambil membuka laptopnya. Catatannya sudah rapi: ada nomor, nama barang, kode pembeli, harga beli, dan harga jual.
Pertanyaannya sederhana. Kalau datanya difilter, misalnya cuma mau lihat Ikan saja, bagaimana caranya tahu jumlah harganya untuk Ikan saja. Beliau sudah mencoba memblok kolomnya lalu menjumlahkan, tapi hasilnya tetap jumlah seluruh baris, termasuk baris yang sedang disembunyikan oleh filter.
Masalah itu ternyata umum, dan jawabannya ada dua. Maka post ini saya isi dengan cara SUM data yang difilter di Excel, lengkap dengan gambar tiap langkahnya, persis seperti yang saya tunjukkan ke beliau waktu itu.
Tonton juga tutorial detailnya di YouTube Channel saya
Kenapa SUM biasa tetap menjumlah baris yang disembunyikan
SUM tidak peduli baris mana yang sedang terlihat di layar. Yang dia lihat cuma rentang selnya. Begitu Anda menulis rumus penjumlahan untuk rentang F6 sampai F22, semua sel di rentang itu ikut dihitung, mau barisnya tampil atau sedang disembunyikan filter.
Filter cuma menyembunyikan baris dari pandangan mata. Isi selnya tetap ada di sana. Jadi wajar kalau hasilnya tidak berubah sedikit pun waktu filternya diganti, dan itu persis yang dikeluhkan tetangga saya.
Yang dibutuhkan adalah rumus yang memang dirancang untuk melewati baris tersembunyi. Di Excel namanya SUBTOTAL.
Data yang saya pakai di contoh ini
Datanya tujuh belas baris, dari baris 6 sampai baris 22, dengan baris judul di baris 5. Kolomnya nomor, item, pembeli, harga beli, dan harga jual. Tombol filter sudah dinyalakan di baris judulnya.
Kode pembeli saya tulis sebagai huruf saja, karena nama pembeli tidak ada hubungannya dengan cara menjumlah. Angka-angkanya pun angka contoh yang dibulatkan, bukan harga asli dagangan beliau.
Cara pertama SUM data yang difilter: rumus SUBTOTAL
Cara ini paling cepat kalau Anda cuma butuh satu angka total dan tidak mau mengubah bentuk datanya sama sekali.
- Klik satu sel kosong di bawah kolom yang mau dijumlah. Di contoh ini sel F23, tepat di bawah kolom harga beli.
- Ketik =SUB. Excel langsung menawarkan SUBTOTAL di daftar saran.
- Pilih SUBTOTAL, lalu ketik kurung buka. Begitu kurungnya dibuka, daftar nomor fungsinya muncul sendiri.
- Pilih nomor 9 – SUM, lalu ketik pemisah argumennya. Di komputer saya pemisahnya titik koma, di komputer lain bisa koma.
- Blok rentang angkanya, di contoh ini F6 sampai F22. Baris judul dan sel totalnya sendiri jangan ikut diblok.
- Tutup kurungnya lalu tekan Enter. Rumus utuhnya jadi =SUBTOTAL(9;F6:F22).
Angka 962.000 itu total semua baris, jadi untuk sementara hasilnya memang sama saja dengan penjumlahan biasa. Bedanya baru kelihatan begitu filternya dipakai.
Sekarang klik tombol filter di kolom Item, hilangkan semua centang, lalu centang Ikan saja, dan tekan OK.
Rumusnya tidak diubah sedikit pun, tapi angkanya ikut. Waktu filternya saya ganti ke Ayam, hasilnya jadi 170.000. Waktu saya ganti lagi ke Tomat, hasilnya jadi 10.000. Itulah yang dicari tetangga saya.
Arti angka 9 dan angka 109 di dalam SUBTOTAL
Angka pertama di dalam SUBTOTAL itu nomor fungsi, bukan sekadar pelengkap. Dia menentukan SUBTOTAL mau menghitung apa, dan sekaligus menentukan baris mana yang dilewati.
- Nomor 1 sampai 11 adalah jenis hitungannya. Angka 9 untuk menjumlah, 1 untuk rata-rata, 2 untuk menghitung banyaknya angka, 4 untuk nilai terbesar, 5 untuk nilai terkecil, dan seterusnya. Tidak akan saya jelaskan satu-persatu disini.
- Nomor 101 sampai 111 isinya sama persis dengan 1 sampai 11, cuma beda perlakuan. Jadi angka 109 juga menjumlah, sama seperti 9.
- Bedanya ada di baris yang Anda sembunyikan sendiri. Angka 9 melewati baris yang disembunyikan filter, tapi tetap menghitung baris yang Anda sembunyikan manual lewat klik kanan lalu Hide. Angka 109 melewati dua-duanya.
Jadi kalau Anda cuma memakai filter, 9 dan 109 hasilnya akan sama. Kalau Anda punya kebiasaan menyembunyikan baris secara manual, pakai 109 supaya hasilnya ikut bersih. Daftar lengkap nomor fungsinya ada di halaman resmi Microsoft untuk fungsi SUBTOTAL.
Cara kedua SUM data yang difilter: Dengan mengubah datanya menjadi Tabel
Cara kedua ini tidak menulis rumus sama sekali. Excel yang menuliskannya untuk Anda, dan hasilnya tetap SUBTOTAL juga.
- Blok seluruh datanya berikut baris judulnya. Di contoh ini dari C5 sampai G22.
- Tekan Ctrl + T. Kotak konfirmasi rentang data langsung muncul.
- Tekan OK. Datanya langsung berganti rupa jadi Tabel, dan tab Table Design muncul di ribbon.
- Buka tab Table Design, lalu buka Table Style Options dan centang Total Row.
- Baris Total muncul sendiri di bawah tabel. Klik selnya, dan di baris rumus terlihat Excel menulis =SUBTOTAL(109;[Harga jual]).
- Untuk kolom lain, klik sel totalnya, lalu klik panah kecil di sebelah kanannya dan pilih Sum.
Perhatikan nomor fungsinya. Excel memakai 109, bukan 9. Jadi baris Total bawaan Tabel sudah otomatis melewati baris tersembunyi, baik yang disembunyikan filter maupun yang Anda sembunyikan sendiri.
Sekarang filternya dipakai seperti biasa, dan dua angkanya ikut berubah bersamaan.
Mana yang sebaiknya dipakai
Dua cara SUM data yang difilter di atas hasilnya sama persis. Yang membedakan cuma kapan sebaiknya dipakai.
- Pakai SUBTOTAL kalau datanya tidak mau diubah bentuknya, atau kalau totalnya mau ditaruh di tempat lain, misalnya di sudut atas lembar kerja.
- Pakai Tabel kalau datanya masih akan bertambah terus. Baris baru yang Anda ketik di bawah tabel otomatis ikut masuk hitungan, sedangkan rumus SUBTOTAL manual harus Anda lebarkan rentangnya sendiri.
- Untuk catatan dagang yang tiap hari nambah baris seperti punya tetangga saya, Tabel jauh lebih enak dipakai.
Berapa baris Ikan yang sedang tampil
Contoh di bagian ini sampai bagian terakhir memakai data biasa. Kalau data Anda sudah jadi Tabel seperti di atas, hasilnya sama persis.
Setelah totalnya ketemu, pertanyaan berikutnya biasanya begini: dari tujuh belas baris itu, berapa kali Ikan terjual? Kalau dihitung dengan COUNTA, hasilnya tetap 17. COUNTA sama seperti SUM, tidak peduli baris mana yang sedang disembunyikan filter.
Jawabannya tetap SUBTOTAL, cuma nomor fungsinya diganti 3. Tulis =SUBTOTAL(3;D6:D22) di sel kosong di luar tabel, misalnya J2, lalu atur filternya ke Ikan. Hasilnya 4, sesuai empat baris Ikan yang tampil. Waktu filternya diganti ke Ayam, hasilnya jadi 2.
Nomor 3 menghitung sel yang tidak kosong, jadi bisa dipakai di kolom teks seperti Item. Nomor 2 hanya menghitung sel yang berisi angka, jadi cocoknya di kolom harga. Di data ini keduanya sama-sama menghasilkan 4 waktu difilter Ikan.
Total Ikan yang tidak ikut berubah saat filter diganti
Ada kalanya total Ikan perlu terus terlihat, sementara filternya dipakai untuk melihat barang lain. SUBTOTAL tidak bisa dipakai untuk ini, karena dia selalu mengikuti filter yang sedang aktif.
Untuk itu pakai SUMIFS. Rumusnya =SUMIFS(F6:F22;D6:D22;"Ikan"), artinya jumlahkan kolom F, tapi hanya baris yang kolom D-nya berisi Ikan.
Kalau filternya diganti ke Ayam, SUBTOTAL berubah jadi 170.000, mengikuti filter. SUMIFS tetap 350.000, karena dia tidak melihat filter sama sekali. Yang dia lihat cuma isi kolom Item.
Jadi pembagiannya begini. SUBTOTAL menjawab berapa total yang sedang terlihat. SUMIFS menjawab berapa total Ikan, apa pun filternya.
Kalau ada sel error di kolom yang dijumlah
Bagaimana kalau salah satu harga ternyata error, misalnya #N/A dari VLOOKUP yang tidak menemukan datanya? Sebagai contoh, matikan dulu filternya, lalu ganti harga beli Ikan di baris 9 jadi #N/A.
SUBTOTAL langsung ikut jadi #N/A. Satu sel error sudah cukup untuk merusak totalnya.
Yang bisa melewati error adalah AGGREGATE. Rumusnya =AGGREGATE(9;7;F6:F22). Angka 9 artinya sama dengan di SUBTOTAL, yaitu menjumlah. Angka 7 artinya lewati baris tersembunyi dan lewati nilai error. Hasilnya 882.000, yaitu 962.000 dikurangi 80.000 dari sel yang error.
Waktu difilter Ikan, AGGREGATE menghasilkan 270.000, yaitu tiga baris Ikan yang tidak error. Yang menarik justru waktu filternya diganti ke Ayam: SUBTOTAL kembali normal jadi 170.000. Baris yang error itu baris Ikan, dan begitu barisnya disembunyikan filter, SUBTOTAL tidak menghitungnya lagi, termasuk error-nya.
AGGREGATE tersedia sejak Excel 2010. Untuk menjumlah sambil melewati error, pakai opsi 6 kalau yang mau dilewati error saja, atau opsi 7 kalau baris tersembunyi juga ikut dilewati.
SUBTOTAL tidak menghitung SUBTOTAL lain
Sebelum lanjut, kembalikan dulu harga beli di baris 9 jadi 80.000 dan matikan filternya, supaya datanya kembali seperti semula.
Masih ingat total di F23 tadi? Kalau suatu hari rentang rumus lain terlanjur ditarik sampai F23, apakah totalnya jadi dobel?
Coba tulis dua rumus ini di sel kosong di luar tabel, misalnya J1 dan J2. =SUM(F6:F23) menghasilkan 1.924.000, dua kali lipat, karena SUM ikut menjumlah total di F23. =SUBTOTAL(9;F6:F23) tetap 962.000. SUBTOTAL sengaja melewati sel yang isinya SUBTOTAL juga, supaya total per kelompok bisa dijumlah lagi tanpa terhitung dua kali.
Itu juga sebabnya fitur Subtotal di tab Data bisa membuat total per kelompok sekaligus total keseluruhan di bawahnya, tanpa angka yang terhitung ganda.
Yang sering bikin hasilnya tetap salah
Empat hal ini yang paling sering bikin SUM data yang difilter tetap meleset, dan semuanya pernah saya temui sendiri.
- Sel totalnya ikut terblok. Kalau rentangnya ditulis sampai F23 padahal rumusnya ada di F23, Excel akan protes karena rumusnya menghitung dirinya sendiri.
- Baris judul ikut terblok. Tidak bikin error, tapi bikin rentangnya tidak rapi dan menyulitkan waktu datanya ditambah.
- Pemisah argumennya salah. Kalau titik koma ditolak, ganti dengan koma. Itu cuma soal pengaturan wilayah di komputer masing-masing, bukan soal rumusnya salah.
- Masih memakai SUM di sebelahnya. Kalau di lembar yang sama masih ada penjumlahan biasa, angkanya memang tidak akan ikut filter, dan itu sering bikin bingung sendiri waktu dua angka itu dibandingkan.
Tetangga saya akhirnya memakai cara kedua, karena catatannya memang bertambah tiap hari dan beliau tidak mau repot mengurus rentang rumus. Buat saya sendiri, yang menarik dari kasus ini bukan rumusnya, tapi kenyataan bahwa satu pertanyaan kecil dari meja dagang di pasar ternyata jawabannya sudah lama disiapkan Excel, cuma jarang ada yang memberitahu.