TL;DR
Rumus SUBTOTAL Excel cuma menghitung baris yang terlihat setelah data difilter, beda dengan SUM yang tetap menjumlahkan semua baris termasuk yang tersembunyi. Sintaksnya =SUBTOTAL(kode_fungsi; range), di mana kode 9 berarti menjumlahkan dan mengabaikan baris yang kena filter, sedangkan kode 109 juga mengabaikan baris yang disembunyikan manual. SUBTOTAL juga otomatis melewati rumus SUBTOTAL lain di dalam range-nya, jadi angka totalmu gak pernah kehitung dua kali.
SUBTOTAL cuma ngitung baris yang keliatan. Begitu kamu filter datanya, angka totalnya ikut nyusut sendiri.
SUM gak gitu. Dia tetap ngitung semua baris di range, termasuk yang lagi disembunyiin filter. Jadi kamu filter cabang Bandung, tapi angka total di bawah masih nunjukin omzet nasional.
Ini bug laporan yang paling sering aku temuin di file Excel orang. Dan perbaikannya cuma ganti satu kata.
SUBTOTAL adalah fungsi Excel yang menghitung nilai dari sebuah range, tapi cuma untuk baris yang lagi terlihat. Baris yang disembunyikan oleh filter otomatis dilewati.
Sintaksnya:
=SUBTOTAL(kode_fungsi; range1; [range2]; ...)kode_fungsi itu angka yang nentuin dia mau ngitung apa. Mau jumlah? Kode 9. Mau rata-rata? Kode 1.
Jadi rumus buat jumlahin data terfilter:
=SUBTOTAL(9; C2:C500)Ada 11 fungsi, masing-masing punya dua versi kode.
| Kode | Kode alt | Fungsi | Ngitung apa |
|---|---|---|---|
| 1 | 101 | AVERAGE | Rata-rata |
| 2 | 102 | COUNT | Jumlah sel berisi angka |
| 3 | 103 | COUNTA | Jumlah sel yang gak kosong |
| 4 | 104 | MAX | Nilai tertinggi |
| 5 | 105 | MIN | Nilai terendah |
| 6 | 106 | PRODUCT | Hasil perkalian |
| 7 | 107 | STDEV | Standar deviasi sampel |
| 8 | 108 | STDEVP | Standar deviasi populasi |
| 9 | 109 | SUM | Total penjumlahan |
| 10 | 110 | VAR | Varians sampel |
| 11 | 111 | VARP | Varians populasi |
Dua-duanya sama-sama ngabaikan baris yang hilang gara-gara filter.
Bedanya cuma di baris yang kamu sembunyiin manual (klik kanan, terus Hide).
Kalau kamu cuma pakai AutoFilter, dua-duanya hasilnya identik. Aturan praktisnya: pakai 109 aja terus, biar aman di dua situasi.
Detail lengkapnya ada di dokumentasi SUBTOTAL di Microsoft Support.
Aku pakai toko_berkah (dataset penjualan UMKM sembako, 1.842 transaksi selama 2025, tersebar di 3 cabang).
Struktur sheet-nya:
| Kolom | Isi |
|---|---|
| A | tanggal |
| B | cabang (Bandung / Cimahi / Soreang) |
| C | kategori |
| D | total_transaksi (rupiah) |
Taruh rumus ini di baris paling atas, di atas header, biar gak ikut kefilter:
=SUBTOTAL(109; D3:D1844) → Total omzet
=SUBTOTAL(103; D3:D1844) → Jumlah transaksi
=SUBTOTAL(101; D3:D1844) → Rata-rata per transaksi
=SUBTOTAL(104; D3:D1844) → Transaksi terbesarTanpa filter apa pun:
| Metrik | Nilai |
|---|---|
| Total omzet | Rp 487.320.000 |
| Jumlah transaksi | 1.842 |
| Rata-rata per transaksi | Rp 264.560 |
Sekarang filter kolom B jadi cuma Soreang. Tanpa nyentuh rumusnya, angkanya langsung berubah:
| Metrik | Nilai (Soreang) |
|---|---|
| Total omzet | Rp 98.145.000 |
| Jumlah transaksi | 512 |
| Rata-rata per transaksi | Rp 191.690 |
Ini angka yang menarik. Soreang nyumbang 28% dari jumlah transaksi, tapi cuma 20% dari omzet. Rata-rata belanja per struk di sana Rp 72.870 lebih rendah dari rata-rata nasional.
Insight itu muncul dalam 5 detik, cuma dari nge-klik dropdown filter. Kalau pakai SUM, semua angka di atas bakal beku di angka nasional dan kamu gak liat apa-apa.
Ini fitur yang paling sering dilupain orang.
Kalau di dalam range SUBTOTAL ada sel lain yang juga berisi SUBTOTAL, sel itu otomatis dilewati.
Jadi kamu bisa bikin struktur kayak gini:
Baris 2-10 : transaksi Sembako
Baris 11 : =SUBTOTAL(109; D2:D10) ← subtotal Sembako
Baris 12-20 : transaksi Minuman
Baris 21 : =SUBTOTAL(109; D12:D20) ← subtotal Minuman
Baris 22 : =SUBTOTAL(109; D2:D21) ← GRAND TOTALGrand total di baris 22 nyakup baris 11 dan 21, yang isinya subtotal juga. Tapi Excel ngelewatin dua baris itu, jadi hasilnya tetap bener. Gak kehitung dua kali.
Coba pakai SUM di baris 22. Hasilnya bakal dua kali lipat. Diam-diam, tanpa error.
Kalau rumus totalnya ada di baris paling bawah tabel, dan baris itu masuk area AutoFilter, dia bisa ikut kesembunyiin waktu kamu filter. Taruh di atas header, atau kasih baris kosong sebagai pemisah.
Sebagian sel pakai SUM, sebagian pakai SUBTOTAL. Waktu difilter, angka-angkanya jadi gak konsisten satu sama lain dan gak ada yang sadar mana yang salah. Pilih satu, pakai konsisten.
=SUBTOTAL(109; D:D) bakal ikut nyakup sel judul, sel total, dan apa pun yang ada di kolom itu. Batasi persis di area datanya.
Sort cuma ngubah urutan, gak nyembunyiin baris. Angka SUBTOTAL gak akan berubah, dan itu emang bener.
Satu sel #DIV/0! bikin seluruh SUBTOTAL jadi error. SUBTOTAL gak bisa ngelompatin error. Kalau ini kejadian, pindah ke rumus AGGREGATE yang punya opsi ngabaikan error.
Bonus yang jarang dipakai. Taruh di kolom A, baris pertama data:
=SUBTOTAL(103; $B$2:B2)Tarik ke bawah. Nomor urutnya bakal selalu 1, 2, 3 berurutan, walaupun datanya lagi difilter. Nomor 1 selalu nempel di baris pertama yang keliatan.
Nomor urut biasa (ketik manual atau =ROW()-1) bakal bolong-bolong pas difilter: 1, 4, 7, 12. Jelek buat laporan yang mau dicetak.
Dua-duanya menjumlahkan dan sama-sama mengabaikan baris yang hilang karena filter. Bedanya di baris yang kamu sembunyikan manual lewat klik kanan lalu Hide. Kode 9 tetap menghitung baris itu, sedangkan kode 109 mengabaikannya. Kalau kamu cuma pakai AutoFilter, dua kode ini hasilnya sama persis.
Kemungkinan besar rumusnya masih pakai SUM, bukan SUBTOTAL. Atau range yang kamu tulis kelewat lebar sampai ikut nyakup baris di luar area filter. Cek juga apakah datanya beneran difilter pakai AutoFilter, bukan cuma disortir. SUBTOTAL cuma bereaksi ke filter dan hide, bukan ke urutan data.
Gak, dan ini fitur yang bikin dia aman dipakai bertingkat. Kalau di tengah range ada sel berisi SUBTOTAL lain, sel itu otomatis dilewati. Jadi kamu bisa naruh subtotal per kategori di tengah tabel, lalu bikin grand total pakai SUBTOTAL juga tanpa takut angkanya kehitung dua kali.
Bisa. Ganti kode fungsinya. Kode 1 buat AVERAGE. Kode 2 buat COUNT. Kode 3 buat COUNTA. Kode 4 MAX, kode 5 MIN. Semua kode ini punya pasangan versi 100-an yang juga mengabaikan baris yang di-hide manual. Jadi satu fungsi ini bisa gantiin 11 fungsi agregat sekaligus.
AGGREGATE bisa semua yang SUBTOTAL bisa, plus dia mampu melewati sel error dan menghitung nilai ke-N tertinggi. Kalau data kamu bersih dan cuma butuh total yang ikut filter, SUBTOTAL lebih pendek dan lebih gampang dibaca. Kalau di kolomnya ada sel #DIV/0! yang bikin total jadi error, baru pindah ke AGGREGATE.
Tiga poin penting soal SUBTOTAL:
Buka satu file laporan yang sering kamu pakai. Cek baris totalnya. Kalau isinya masih =SUM(...) padahal tabelnya ada filter, ganti sekarang. Butuh 30 detik.
Mau latihan langsung sama datanya? Ada di halaman fungsi SUBTOTAL. Istilah AutoFilter aku jelasin di glosarium filter.
Lanjut baca: Rumus AGGREGATE Excel (versi SUBTOTAL yang bisa lompatin sel error).
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Cara pakai rumus FV dan PV Excel buat hitung nilai uang di masa depan dan sekarang. Lengkap dengan contoh tabungan dan target modal UMKM.
Investasi kelihatan untung di atas kertas, tapi nilai uang berubah tiap tahun. NPV dan IRR Excel bantu kamu nilai apakah proyek beneran layak, bukan cuma kelihatan untung.
Sebelum ambil pinjaman, kamu bisa hitung sendiri cicilan bulanannya pakai satu rumus. Ini cara PMT Excel bekerja, lengkap dengan contoh modal usaha dan jebakan bunga tahunan.