Skip to main content
Home  ›  Excel

Excel: SUBTOTAL vs AGGREGATE untuk Data Terfilter & Baris Tersembunyi (Biar Rekap Tidak Salah)

Diperbarui: 22 Desember 2025

Ringkasan cepat:

  • Kalau kamu sudah filter tabel tapi total masih “semua data”, biasanya kamu masih pakai SUM/COUNT biasa.
  • SUBTOTAL cocok untuk rekap cepat yang mengikuti baris terlihat saat filter aktif.
  • Untuk baris yang kamu Hide Rows manual: SUBTOTAL punya dua versi nomor (mis. 9 vs 109) agar kamu bisa pilih apakah baris tersembunyi ikut dihitung atau tidak.
  • AGGREGATE cocok kalau data sering error (#N/A, #DIV/0!) atau kamu butuh MEDIAN/TOP-N pada data yang sedang difilter.
  • Prinsip aman: rapikan data dulu (angka jangan jadi teks), baru rekap—biar keputusan bisnis/kerja tidak meleset.

Daftar isi

Kenapa total rekap sering salah saat filter/hidden?

Biasanya masalahnya bukan karena Excel “error”, tapi karena rumus yang kamu pakai memang tidak peduli baris mana yang sedang ditampilkan.

  • Kamu filter “Sales = Andi”, tapi total masih menjumlahkan semua sales.
  • Kamu hide beberapa baris manual, tapi total tetap masuk (atau malah sebaliknya).
  • Ada sel error (#N/A, #DIV/0!) di kolom nominal, lalu total ikut error atau hasilnya terasa “aneh”.

Kalau kamu masih di level dasar, kamu bisa mulai dari artikel: Cara Menghitung Jumlah di Excel untuk Pemula: SUM, AutoSum, SUMIF, SUMIFS, dan SUBTOTAL (Lengkap + Contoh Tabel). Setelah paham SUM/SUMIF/SUMIFS, artikel ini akan terasa jauh lebih gampang.

SUBTOTAL vs AGGREGATE: bedanya apa?

1) SUBTOTAL

  • Fokusnya: rekap cepat untuk data yang difilter.
  • Mendukung fungsi inti seperti SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, dll.
  • Untuk Hide Rows manual, kamu pilih versi nomor:
    • 1–11 = baris yang di-hide manual tetap dihitung
    • 101–111 = baris yang di-hide manual diabaikan

2) AGGREGATE

  • Fokusnya: rekap yang lebih “tahan banting”.
  • Bisa mengabaikan hidden rows dan/atau error values tergantung opsi.
  • Punya lebih banyak operasi (misalnya MEDIAN, LARGE/SMALL, percentile), jadi cocok untuk analisis yang lebih “advance”.

Langkah 1–4: Pakai SUBTOTAL untuk total yang ikut filter

1. Rapikan data dulu (biar rumus tidak menipu)

Minimal pastikan 3 hal ini:

  • Kolom nominal benar-benar angka (bukan “angka yang terbaca teks”).
  • Format tanggal konsisten (jangan campur teks tanggal dan tanggal asli Excel).
  • Jangan banyak merge cell di area data (merge sering bikin sort/filter berantakan).

Kalau kamu sering mengalami SUM/SUMIF hasil 0 atau tidak menghitung setelah copy-paste dari WhatsApp/web/PDF, ikuti langkah perbaikannya di: Rumus SUM/SUMIF di Excel Tidak Menghitung atau Hasil 0? 12 Penyebab + Cara Memperbaiki (Lengkap Contoh Tabel).

2. Buat total yang mengikuti filter (SUM)

Misal nominal ada di D2:D10000. Pakai:

  • =SUBTOTAL(9,D2:D10000) → SUM mengikuti filter, dan baris yang kamu hide manual tetap dihitung.
  • =SUBTOTAL(109,D2:D10000) → SUM mengikuti filter, dan baris yang kamu hide manual diabaikan.

Tips cepat ingatnya: angka “100-an” (109, 103, 101, dst.) biasanya versi yang mengabaikan baris Hide Rows manual.

3. Hitung jumlah baris terlihat (COUNTA) setelah difilter

Misal kamu ingin menghitung berapa baris “terlihat” di kolom Status (E2:E10000):

  • =SUBTOTAL(3,E2:E10000) → COUNTA mengikuti filter, baris hide manual tetap dihitung.
  • =SUBTOTAL(103,E2:E10000) → COUNTA mengikuti filter, baris hide manual diabaikan.

4. Catatan penting agar tidak “dobel hitung”

  • Jangan menaruh SUBTOTAL di dalam range yang juga ikut kamu SUBTOTAL lagi (hindari nested yang tidak jelas).
  • Paling aman: 1 kolom = 1 jenis nilai (Nominal), lalu totalnya 1 rumus SUBTOTAL di bagian bawah/atas tabel.

Langkah 5–7: Pakai AGGREGATE (lebih kebal error)

5. Kapan kamu butuh AGGREGATE?

  • Di kolom nominal ada error (#N/A, #DIV/0!), tapi kamu tetap butuh total/median.
  • Kamu butuh MEDIAN atau “Top-3 terbesar” dari data yang sedang difilter.
  • Kamu ingin kontrol: mau mengabaikan error saja, atau sekaligus hidden rows, atau juga mengabaikan subtotal lain.

6. Contoh rumus AGGREGATE yang paling sering dipakai

Misal nominal tetap di D2:D10000:

  • SUM (abaikan error): =AGGREGATE(9,6,D2:D10000)
  • MEDIAN (abaikan error): =AGGREGATE(12,6,D2:D10000)
  • Nilai ke-3 terbesar (LARGE) (abaikan error): =AGGREGATE(14,6,D2:D10000,3)

7. Rekap lintas sheet? Pastikan referensi rapi

Kalau data ada di sheet Transaksi dan rekap di sheet Rekap, kamu bisa pakai:

  • =SUBTOTAL(109,Transaksi!D2:D10000)
  • =AGGREGATE(9,6,Transaksi!D2:D10000)

Kalau kamu butuh panduan lengkap referensi antar sheet (Sheet1!A1), rekap otomatis lintas sheet, sampai hyperlink lompat sheet, baca: Link Excel Beda Sheet: Cara Mengambil Data Antar Sheet (Sheet1!A1), Membuat Rekap Otomatis, dan Hyperlink Lompat Sheet (Lengkap + Contoh Tabel).

Contoh kasus: rekap penjualan & absensi

1) Rekap penjualan: total ikut filter Sales/Channel

Misal kamu filter “Channel = Marketplace”.

  • =SUM(D2:D10000) → tetap menjumlahkan semua baris, termasuk yang tidak terlihat.
  • =SUBTOTAL(109,D2:D10000) → total mengikuti baris yang terlihat setelah filter.

Kalau targetmu rekap tahunan yang rapi (omzet per bulan/per produk/per sales) tanpa bikin 12 sheet manual, lanjutkan ke: Rekap Penjualan Tahunan di Excel dengan Pivot Table: Omzet per Produk, per Bulan, dan per Sales (Cepat).

2) Rekap absensi: hitung “Hadir” yang terlihat setelah difilter

Filter Status = “Hadir”, lalu hitung berapa baris Status yang terlihat:

  • =SUBTOTAL(103,E2:E10000)

3) Jika kamu ingin 1 halaman ringkasan yang bisa difilter

Setelah rekap beres, tahap berikutnya biasanya bikin dashboard 1 halaman (KPI + grafik + filter cepat). Panduannya ada di: Dashboard Laporan Tahunan di Excel: Pivot Table + Slicer (Filter Cepat) untuk Penjualan/HR dalam 1 Halaman.

Checklist cepat memilih rumus

  • Butuh total ikut filter saja → pakai SUBTOTAL(109,...) (paling aman untuk SUM).
  • Mau baris Hide Rows manual tetap dihitung → pakai SUBTOTAL(9,...).
  • Data sering ada error → pakai AGGREGATE(...) dengan opsi yang mengabaikan error.
  • Butuh MEDIAN / TOP-N / analisis lebih “tajam” → cenderung pilih AGGREGATE.
  • Untuk laporan besar yang sering kamu refresh → pertimbangkan Pivot + Slicer (lebih stabil daripada rumus yang panjang).

FAQ SUBTOTAL vs AGGREGATE

1. SUBTOTAL itu selalu ikut filter?

Iya. SUBTOTAL mengabaikan baris yang tidak terlihat karena filter. Perdebatan biasanya hanya soal: baris yang kamu Hide Rows manual mau dihitung atau tidak (pakai 1–11 vs 101–111).

2. Kenapa SUBTOTAL saya “tidak sesuai” setelah filter?

Penyebab paling sering: range salah (kurang/kelebihan), angka terbaca teks, atau ada error di kolom nominal. Kalau error sering muncul, pertimbangkan AGGREGATE.

3. Apakah SUBTOTAL/AGGREGATE cocok untuk range horizontal?

Umumnya tidak ideal. Dua fungsi ini lebih aman untuk kolom/vertical range (data transaksi) daripada baris/horizontal range.

4. Kalau saya pakai Excel Table (Format as Table), bisa pakai SUBTOTAL?

Bisa. Contoh: =SUBTOTAL(109,Table1[Nominal]). Ini enak karena range otomatis mengikuti data yang bertambah.

5. Jika sudah pakai Pivot Table, masih perlu SUBTOTAL/AGGREGATE?

Pivot biasanya sudah cukup untuk laporan besar. SUBTOTAL/AGGREGATE tetap berguna untuk rekap cepat di luar Pivot atau saat kamu butuh hasil yang “ikut filter” tanpa membangun Pivot dulu.

Baca juga di Beginisob.com

Comments

Post a Comment

Edukasi Terpopuler

Connect With Us

Copyright @ 2023 beginisob.com, All right reserved