Cara Menggunakan Rumus SUBTOTAL Di Excel | Hitung Data Terfilter Dengan Mudah
Kenapa SUM Biasa Gagal Saat Data Difilter?
Bayangkan kamu punya tabel penjualan 200 baris, lalu kamu memfilter hanya produk "Brosur" untuk melihat totalnya. Kalau kamu memakai SUM biasa, hasilnya tetap menjumlahkan semua baris, termasuk yang sedang tersembunyi oleh filter. Inilah masalah yang sering membuat laporan terlihat salah padahal rumusnya tidak rusak. Rumus SUBTOTAL hadir untuk itu: ia hanya menghitung baris yang terlihat, sehingga totalnya selalu mengikuti filter yang kamu pasang.
Di artikel ini kamu akan belajar memakai SUBTOTAL dari nol: arti kode fungsinya, cara menghitung total data yang sudah difilter, cara membuat subtotal bertingkat per kategori, sampai perbedaan SUBTOTAL dengan SUM. Semua contoh memakai data penjualan sederhana yang gampang kamu tiru.
Langkah 1: Kenali Sintaks Dan Kode Fungsi SUBTOTAL
Rumus SUBTOTAL punya dua bagian: kode fungsi dan rentang data. Bentuk dasarnya seperti ini:
=SUBTOTAL(kode_fungsi; rentang)
Kode fungsi menentukan perhitungan apa yang dilakukan. Kode yang paling sering dipakai adalah 1 untuk AVERAGE, 2 untuk COUNT, 3 untuk COUNTA, 4 untuk MAX, 5 untuk MIN, dan 9 untuk SUM. Ada juga kode 101 sampai 109 yang fungsinya sama, tetapi mengabaikan baris yang disembunyikan secara manual, bukan hanya yang tersembunyi karena filter. Misalnya untuk menjumlahkan penjualan di kolom C2:C20, rumusnya:
=SUBTOTAL(9; C2:C20)
Angka 9 berarti SUM. Kalau tidak ada filter aktif, hasilnya sama persis dengan =SUM(C2:C20). Bedanya baru terasa begitu kamu memfilter datanya: SUM tetap menghitung semua baris, sedangkan SUBTOTAL hanya menghitung baris yang terlihat.
Langkah 2: Hitung Total Data Yang Sudah Difilter
Sekarang coba praktik langsung. Siapkan tabel penjualan dengan kolom Produk di A, Kota di B, dan Jumlah di C. Pasang filter lewat menu Data lalu Filter, kemudian filter kolom Produk hanya untuk "Spanduk". Di sel di bawah tabel, tulis rumus berikut:
=SUBTOTAL(9; C2:C20)
Hasilnya langsung berubah mengikuti filter: hanya penjualan spanduk yang dijumlahkan. Ganti filternya ke "Kartu Nama", dan totalnya ikut berubah tanpa kamu mengedit rumusnya. Inilah alasan SUBTOTAL wajib dipakai di setiap laporan yang sering difilter, misalnya laporan penjualan harian, rekap order, atau daftar stok yang kamu saring per kategori.
Laporan Sudah Rapi? Cetak Jadi Dokumen Yang Profesional!
Laporan penjualan yang kamu hitung dengan SUBTOTAL bisa langsung dicetak jadi laporan atau dokumen yang rapi. Website buka 24 jam, jadi kamu bisa order kapan saja.
🖨️ Cetak Laporan & Dokumen Kamu Di SiniOrder via Website (24 Jam) — praktis, cepat, dan bisa dilakukan kapan saja.
Langkah 3: Buat Subtotal Bertingkat Per Kategori
SUBTOTAL juga bisa dipakai untuk membuat subtotal per kelompok, misalnya total penjualan per kota. Caranya, urutkan dulu datanya berdasarkan kolom Kota lewat menu Data lalu Sort. Setelah datanya berkelompok, pilih menu Data lalu Subtotal. Di jendela yang muncul, pilih kolom "Kota" sebagai pemutus kelompok, pilih fungsi Sum, dan centang kolom Jumlah yang mau dijumlahkan, lalu klik OK.
Excel akan menyisipkan baris subtotal otomatis di setiap pergantian kota, lengkap dengan tombol plus dan minus di sisi kiri untuk melipat atau membuka kelompoknya. Kamu bisa memakai tombol angka 1, 2, dan 3 di pojok kiri atas untuk menampilkan hanya grand total, subtotal per kota, atau semua detail. Teknik ini sangat berguna untuk laporan bulanan yang dikelompokkan per cabang, per produk, atau per sales.
Langkah 4: Pakai Kode 101 Sampai 109 Untuk Baris Yang Disembunyikan Manual
Ada perbedaan penting antara kode 1 sampai 11 dengan kode 101 sampai 109. Kode 9 hanya mengabaikan baris yang tersembunyi karena filter, tetapi tetap menghitung baris yang kamu sembunyikan manual lewat klik kanan lalu Hide. Sebaliknya, kode 109 mengabaikan keduanya. Rumus ini menjumlahkan hanya baris yang benar-benar terlihat:
=SUBTOTAL(109; C2:C20)
Pakai kode 109 kalau laporanmu sering menyembunyikan baris tertentu secara manual, misalnya baris data lama yang belum mau dihapus. Untuk laporan yang hanya memakai filter, kode 9 sudah cukup. Ingat juga: SUBTOTAL mengabaikan SUBTOTAL lain di dalam rentangnya, jadi kamu bisa menaruh subtotal per kelompok lalu grand total di bawahnya tanpa takut angkanya terhitung ganda.
Kesalahan Yang Sering Terjadi
- Memakai SUM di laporan yang difilter. SUM menghitung semua baris, termasuk yang tersembunyi. Kalau laporanmu memakai filter, ganti SUM dengan SUBTOTAL berkode 9 supaya totalnya mengikuti filter.
- Salah kode fungsi. Kode 9 adalah SUM, kode 1 adalah AVERAGE, kode 3 adalah COUNTA. Kalau kamu menulis 1 padahal maunya jumlah, hasilnya jadi rata-rata. Hafalkan kode 9 untuk jumlah dan 1 untuk rata-rata.
- Filter tidak aktif saat memeriksa hasil. Tanpa filter, SUBTOTAL dan SUM hasilnya sama. Banyak orang mengira SUBTOTAL tidak bekerja padahal filternya memang belum dipasang. Pastikan filter aktif lewat menu Data lalu Filter.
- Subtotal bertingkat tanpa mengurutkan data dulu. Fitur Data lalu Subtotal mengelompokkan berdasarkan urutan baris. Kalau datanya tidak diurutkan per kategori dulu, subtotalnya akan muncul di tempat yang salah dan berantakan.
- Menjumlahkan sel yang berisi SUBTOTAL lain dengan SUM. Kalau grand total memakai SUM dan rentangnya mencakup subtotal per kelompok, angkanya terhitung ganda. Pakai SUBTOTAL juga untuk grand total, atau pastikan rentangnya hanya mencakup data asli.
Latihan Cepat
Coba sekarang: buat tabel 10 baris berisi kolom Produk, Kota, dan Jumlah. Di bawah tabel tulis =SUBTOTAL(9; C2:C11), lalu filter kolom Produk untuk satu produk saja dan perhatikan totalnya berubah. Berikutnya, filter kolom Kota dan lihat totalnya ikut berubah lagi. Terakhir, urutkan data berdasarkan Kota, lalu pakai menu Data lalu Subtotal untuk membuat subtotal per kota. Kalau ketiganya berhasil, kamu sudah menguasai SUBTOTAL untuk membuat laporan yang selalu akurat walau datanya difilter berkali-kali. Selamat mencoba!