Menjumlah Data yang Difilter di Excel: SUBTOTAL vs AGGREGATE (Biar Rekap Tidak Salah Saat Filter Aktif)
Diperbarui: 20 Desember 2025
Ringkasan cepat:
- Kalau datamu sering difilter dan kamu ingin menjumlah hanya baris yang terlihat, pakai SUBTOTAL atau AGGREGATE (jangan SUM biasa).
- SUBTOTAL paling mudah untuk pemula: contoh paling aman untuk jumlah visible = =SUBTOTAL(109,range).
- AGGREGATE cocok kalau data kamu “kotor” (ada error seperti #DIV/0!) karena bisa abaikan error: contoh = AGGREGATE(9,7,range).
Daftar isi
- Kapan kamu wajib pakai SUBTOTAL/AGGREGATE?
- Apa bedanya SUM vs SUBTOTAL vs AGGREGATE?
- Syarat & persiapan (biar hasil tidak “ngaco”)
- Diagnosis cepat (decision tree)
- Checklist 1–3 menit sebelum bikin rekap
- Langkah-langkah (contoh nyata + rumus per sel)
- Tabel cepat: masalah → penyebab → solusi
- Tips penting (filter, hide, dan “double count”)
- Risiko & kesalahan umum
- FAQ
- Baca juga di Beginisob.com
Kapan kamu wajib pakai SUBTOTAL/AGGREGATE?
Kamu wajib pakai SUBTOTAL/AGGREGATE kalau:
- Kamu bikin laporan lalu sering pakai Filter (Data → Filter) untuk menampilkan kategori tertentu.
- Kamu ingin total ikut berubah otomatis sesuai baris yang terlihat.
- Kamu punya beberapa subtotal di bawah (rekap per kategori) dan takut terjadi double count.
- Datamu kadang ada error (#DIV/0!, #VALUE!) dan kamu butuh total tetap jalan.
Apa bedanya SUM vs SUBTOTAL vs AGGREGATE?
- SUM: menjumlah semua nilai di range, tidak peduli filter. Kalau kamu filter data, SUM tetap menjumlah baris yang tersembunyi.
- SUBTOTAL: bisa menjumlah hanya yang terlihat saat filter. Paling sering dipakai untuk rekap cepat.
- AGGREGATE: mirip SUBTOTAL, tapi lebih “kuat” karena bisa diatur untuk mengabaikan error dan punya opsi perilaku yang lebih banyak.
Syarat & persiapan (biar hasil tidak “ngaco”)
- Pastikan kolom angka benar-benar angka, bukan teks (cek: angka rata kanan, bukan kiri).
- Gunakan range kolom (vertikal) seperti F2:F11, jangan range horizontal panjang.
- Kalau kamu bikin “subtotal per bagian”, pastikan subtotal di bagian bawah tidak menjumlah subtotal lain (pakai SUBTOTAL/AGGREGATE yang memang menghindari double count).
- Kalau datamu berpotensi ada error, siapkan rumus versi AGGREGATE untuk cadangan.
Diagnosis cepat (decision tree)
- Butuh total tidak berubah meski filter? → pakai SUM.
- Butuh total ikut filter (visible only) dan datamu bersih (tanpa error)? → pakai SUBTOTAL(109,…).
- Butuh total ikut filter tapi sering ada #DIV/0! / error? → pakai AGGREGATE(9,7,…) (abaikan hidden + error).
- Butuh hitung selain SUM (mis. MEDIAN, LARGE/SMALL) sambil abaikan error? → pakai AGGREGATE (function_num 12–19 juga ada).
Checklist 1–3 menit sebelum bikin rekap
- Sudah pasang Filter (ikon panah di header kolom)?
- Total yang kamu pakai di bawah adalah SUBTOTAL/AGGREGATE (bukan SUM)?
- Kamu pakai SUBTOTAL 109 (agar baris yang disembunyikan manual juga tidak ikut)?
- Jika data mungkin error, kamu siapkan versi AGGREGATE option 7?
- Kamu uji cepat: filter 1 kategori → total berubah; hapus filter → total balik normal.
Langkah-langkah (contoh nyata + rumus per sel)
Contoh tabel penjualan (kolom A, B, C…)
Copy tabel ini ke Excel mulai dari sel A1.
| Kolom A | Kolom B | Kolom C | Kolom D | Kolom E | Kolom F |
|---|---|---|---|---|---|
| Tanggal | Produk | Kategori | Qty | Harga | Nilai |
| 01/12/2025 | Kopi | Minuman | 2 | 18000 | (rumus) |
| 01/12/2025 | Keripik | Snack | 3 | 15000 | (rumus) |
| 02/12/2025 | Teh | Minuman | 5 | 8000 | (rumus) |
| 02/12/2025 | Roti | Snack | 2 | 12000 | (rumus) |
| 03/12/2025 | Kopi | Minuman | 1 | 18000 | (rumus) |
| 03/12/2025 | Susu | Minuman | 2 | 19000 | (rumus) |
| 04/12/2025 | Keripik | Snack | 1 | 15000 | (rumus) |
| 04/12/2025 | Teh | Minuman | 2 | 8000 | (rumus) |
| 05/12/2025 | Roti | Snack | 4 | 12000 | (rumus) |
| 05/12/2025 | Kopi | Minuman | 3 | 18000 | (rumus) |
Arti kolom:
- A = tanggal transaksi
- B = nama produk
- C = kategori (ini yang sering kamu filter)
- D = jumlah barang
- E = harga satuan
- F = nilai penjualan (Qty × Harga)
Langkah 1 — Buat rumus Nilai (kolom F)
Di sel F2 tulis:
=D2*E2
Lalu tarik ke bawah sampai F11.
Langkah 2 — Pasang Filter
- Klik salah satu sel pada header (baris 1).
- Masuk menu Data → Filter.
- Di kolom Kategori (kolom C) akan muncul ikon filter.
Langkah 3 — Bandingkan 3 cara menjumlah: SUM vs SUBTOTAL vs AGGREGATE
Tulis label dan rumus di bawah tabel (misal mulai baris 13):
| Sel | Tujuan | Rumus | Catatan singkat |
|---|---|---|---|
| E13 / F13 | Total dengan SUM | =SUM(F2:F11) | Tidak peduli filter (sering bikin rekap salah saat filter aktif) |
| E14 / F14 | Total visible dengan SUBTOTAL | =SUBTOTAL(109,F2:F11) | Umumnya pilihan paling aman untuk rekap saat filter |
| E15 / F15 | Total visible + abaikan error (AGGREGATE) | =AGGREGATE(9,7,F2:F11) | Kalau ada error di data, total tetap jalan |
Langkah 4 — Uji cepat (ini yang membuat kamu yakin rumusnya benar)
- Filter kolom Kategori menjadi Minuman saja.
- Lihat hasil: SUM (F13) biasanya tetap sama, sedangkan SUBTOTAL (F14) dan AGGREGATE (F15) mengikuti hasil filter.
- Hapus filter (All) → SUBTOTAL/AGGREGATE kembali ke total semua data.
Catatan penting tentang angka SUBTOTAL (9 vs 109)
- SUBTOTAL(9,range) = SUM, tetapi masih mengikutkan baris yang kamu hide manual.
- SUBTOTAL(109,range) = SUM, dan mengabaikan baris yang hide manual (lebih aman untuk rekap).
- Kalau baris “hilang” karena Filter, SUBTOTAL memang dibuat untuk mengabaikannya (itulah tujuan utamanya).
Tabel cepat: masalah → penyebab → solusi
| Masalah | Penyebab paling umum | Solusi yang benar |
|---|---|---|
| Total tidak berubah walau filter aktif | Kamu pakai SUM biasa | Ganti ke =SUBTOTAL(109,range) atau =AGGREGATE(9,7,range) |
| Total jadi error (#DIV/0!) | Di range ada error dari rumus lain | Pakai AGGREGATE dengan opsi abaikan error: =AGGREGATE(9,7,range) |
| Total masih menghitung baris yang kamu hide manual | Kamu pakai SUBTOTAL 1–11 (mis. 9) | Ganti ke SUBTOTAL 101–111 (mis. 109 untuk SUM) |
| Angka rekap dobel (double count) karena subtotal bertingkat | Subtotal menjumlah subtotal lain | Gunakan SUBTOTAL/AGGREGATE (keduanya dirancang untuk menghindari subtotal bersarang dihitung ulang) |
| Rekap tidak bereaksi saat kamu hide kolom (bukan hide baris) | SUBTOTAL/AGGREGATE fokusnya pada baris (range vertikal) | Pastikan rekap di kolom (vertikal) dan yang disembunyikan adalah baris, bukan kolom |
Tips penting (filter, hide, dan “double count”)
- Kalau tujuanmu “jumlah yang tampil di layar”, default paling aman: SUBTOTAL(109,…).
- Kalau datamu sering berisi error, biasakan pakai AGGREGATE(9,7,…) sebagai “versi tahan banting”.
- Jangan menaruh rumus SUBTOTAL/AGGREGATE di dalam range yang dia jumlahkan (hindari referensi melingkar).
- Kalau kamu bikin rekap per kategori, pertimbangkan 2 cara: (1) filter + SUBTOTAL/AGGREGATE, atau (2) SUMIFS untuk rekap per kategori tanpa filter (pilih sesuai kebutuhan).
Risiko & kesalahan umum
- Salah pilih angka SUBTOTAL (pakai 9 padahal butuh 109) → baris yang di-hide manual masih ikut.
- Data angka jadi teks → hasil jumlah tidak sesuai. Solusi: ubah format, atau konversi teks ke angka.
- Mengira SUM itu mengikuti filter → ini sumber salah rekap paling sering pada pemula.
- Range salah (kepotong) → total beda sedikit tapi sulit ketahuan. Biasakan pakai tabel yang rapi dan cek baris terakhir.
FAQ
1) Kenapa SUM tidak ikut berubah saat filter?
Karena SUM memang menjumlah semua sel di range, bukan “yang terlihat”. Kalau butuh yang terlihat, pakai SUBTOTAL atau AGGREGATE.
2) SUBTOTAL yang paling aman untuk menjumlah saat filter itu nomor berapa?
Untuk jumlah (SUM), yang paling aman biasanya 109: =SUBTOTAL(109,range).
3) Kapan sebaiknya pakai AGGREGATE dibanding SUBTOTAL?
Saat data kamu berpotensi ada error (#DIV/0!, #VALUE!) atau kamu butuh opsi tambahan (misalnya abaikan error). Contoh aman: =AGGREGATE(9,7,range).
4) AGGREGATE(9,7,range) itu artinya apa?
9 berarti fungsi SUM, dan 7 berarti abaikan baris tersembunyi + abaikan error.
5) Kalau saya tidak pakai filter, masih perlu SUBTOTAL?
Kalau tidak butuh “visible only”, SUM saja cukup. Tapi SUBTOTAL tetap berguna kalau kamu memakai Outline/Subtotal atau ingin menghindari double count pada subtotal bertingkat.
Comments
Post a Comment