Rumus SUBTOTAL Excel: Hitung Data yang Sedang Difilter Saja

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.

Apa itu rumus SUBTOTAL di Excel?

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)

Apa aja kode fungsi SUBTOTAL?

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

Bedanya kode 9 sama 109 apa?

Dua-duanya sama-sama ngabaikan baris yang hilang gara-gara filter.

Bedanya cuma di baris yang kamu sembunyiin manual — klik kanan, Hide.

• Kode 9: baris yang di-hide manual tetap dihitung
• Kode 109: baris yang di-hide manual diabaikan

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.

Contoh kasus: laporan penjualan toko_berkah

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 terbesar

Tanpa 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.

Kenapa SUBTOTAL gak double count?

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 TOTAL

Grand 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.

Kesalahan umum waktu pakai SUBTOTAL

1. Naruh rumus SUBTOTAL di dalam area yang difilter

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.

2. Nyampur SUM sama SUBTOTAL di satu laporan

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.

3. Range-nya kelewat lebar

=SUBTOTAL(109; D:D) bakal ikut nyakup sel judul, sel total, dan apa pun yang ada di kolom itu. Batasi persis di area datanya.

4. Ngira SUBTOTAL bereaksi ke sort

Sort cuma ngubah urutan, gak nyembunyiin baris. Angka SUBTOTAL gak akan berubah, dan itu emang bener.

5. Ada sel error di dalam range

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.

Trik: bikin nomor urut yang ikut filter

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.

FAQ

Apa bedanya SUBTOTAL 9 dan 109?

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.

Kenapa SUBTOTAL nggak ikut berubah waktu data difilter?

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.

Apakah SUBTOTAL menghitung rumus SUBTOTAL lain di dalam range-nya?

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.

SUBTOTAL bisa dipakai buat rata-rata dan hitung baris nggak?

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.

Lebih bagus SUBTOTAL atau AGGREGATE?

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!, baru pindah ke AGGREGATE.

Penutup

Tiga hal buat dibawa pulang:

1. SUM ngitung semua baris. SUBTOTAL cuma yang keliatan. Ganti SUM jadi SUBTOTAL di semua baris total laporan kamu.
2. Pakai kode 109 buat jumlah, 101 buat rata-rata, 103 buat hitung baris. Aman di semua situasi.
3. SUBTOTAL gak ngitung SUBTOTAL lain, jadi total bertingkat gak bakal double count.

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.