Rumus SUBTOTAL Excel: Total yang Abaikan Subtotal dan Filter

=SUBTOTAL(9,C2:C8) menjumlahkan angka di C2:C8, seperti SUM, dengan dua perbedaan: rumus ini melewati rumus SUBTOTAL lain di dalam rentang, dan melewati baris yang disembunyikan filter. Argumen pertama, 9, menentukan perhitungan yang dilakukan.

RumusReset

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Total keseluruhan di C8 mencakup seluruh kolom, termasuk baris subtotal, dan tetap menampilkan 445: SUBTOTAL tidak mengikutkan C4 dan C7 karena keduanya berisi rumus SUBTOTAL. F2 melakukan hal yang sama dengan SUM dan menampilkan 890, setiap penjualan dihitung dua kali. Dengan SUBTOTAL di setiap baris total, kamu bisa menambah atau memindahkan kelompok tanpa menulis ulang total keseluruhannya.

Nomor fungsi SUBTOTAL

=SUBTOTAL(function_num, ref1, [ref2], ...)

Saat kamu mengetik =SUBTOTAL(, Excel menampilkan daftar ini, jadi kamu tidak perlu menghafalnya. 9 dan 109 (SUM), 1 (AVERAGE) dan 103 (menghitung baris yang terlihat) adalah yang paling sering dipakai.

RumusReset

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Tidak ada yang disembunyikan di sini, jadi setiap baris sama dengan fungsi biasanya: rata-rata 88.33, 6 baris, MAX 200, MIN 30 dan SUM 530. Perbedaannya baru terlihat ketika ada baris yang disembunyikan, dan itulah isi bagian berikutnya.

SUBTOTAL 9 vs 109, dan baris yang difilter

Aktifkan filter dengan Data > Filter (Ctrl+Shift+L, Cmd+Shift+F di Mac), lalu pilih North di drop-down Region. Baris dari wilayah lain disembunyikan:

• =SUM(C2:C7) tetap menjumlahkan keenam baris.
• =SUBTOTAL(9,C2:C7) dan =SUBTOTAL(109,C2:C7) hanya menjumlahkan baris North yang terlihat.
• =SUBTOTAL(103,A2:A7) menghitung baris yang tersisa di layar: 2, sama dengan "2 of 6 records found" di status bar.

Kedua kelompok ini hanya berbeda untuk baris yang kamu sembunyikan secara manual (pilih baris, klik kanan > Hide). 9 tetap menjumlahkannya; 109 tidak. Kalau totalnya harus selalu sama dengan yang terlihat di layar, pakai 109. Kalau kamu menyembunyikan baris hanya untuk merapikan tampilan dan tetap ingin baris itu ikut dalam total, pakai 9.

SUBTOTAL hanya bekerja pada baris. Kolom yang disembunyikan selalu ikut dihitung, jadi =SUBTOTAL(109,B2:G2) di sepanjang sebuah baris juga menjumlahkan kolom yang tersembunyi.

Cara tercepat mendapatkan SUBTOTAL adalah tombol AutoSum saat filter aktif: Excel menulis =SUBTOTAL(9,...), bukan SUM. Data > Subtotal melangkah lebih jauh: pada daftar yang diurutkan berdasarkan sebuah kolom, fitur ini menyisipkan baris total di bawah setiap kelompok dan total keseluruhan, semuanya dengan SUBTOTAL, ditambah tombol outline untuk menciutkan kelompoknya.

AGGREGATE: SUBTOTAL yang bisa melewati error

Kalau ada satu sel di rentang yang berisi error, SUM dan SUBTOTAL mengembalikan error itu. AGGREGATE (Excel 2010 dan versi sesudahnya) adalah SUBTOTAL dengan argumen opsi tambahan; opsi 6 mengabaikan nilai error.

RumusReset

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

C3 berisi #N/A, jadi F2 juga menampilkan #N/A. F3 mengabaikannya dan menjumlahkan lima sisanya: 450. Argumen pertamanya memakai nomor yang sama dengan SUBTOTAL (9 adalah SUM, 4 adalah MAX). Opsi lainnya: 5 mengabaikan baris tersembunyi, 7 mengabaikan baris tersembunyi dan error, 3 mengabaikan baris tersembunyi, error, dan rumus SUBTOTAL serta AGGREGATE di dalamnya. Ganti C3 dengan sebuah angka, maka F2 menampilkan total yang sama dengan F3.

Latihan: total keseluruhan di atas subtotal

RumusReset

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Giliranmu: Daftar ini punya subtotal di bawah setiap wilayah. Taruh total keseluruhan di C9 yang mencakup C2:C8 tanpa menghitung baris subtotal dua kali.

PeriksaTampilkan jawaban

Kenapa total SUBTOTAL masih salah

• Total kelompoknya memakai SUM. SUBTOTAL melewati rumus SUBTOTAL lain di dalam rentangnya, bukan rumus SUM. Total kelompok yang ditulis sebagai =SUM(C2:C3) akan dihitung lagi. Ubah setiap baris total menjadi SUBTOTAL.
• Barisnya disembunyikan secara manual dan nomor fungsinya 9. Pakai 109.
• Datanya ada di kolom, bukan di baris. Kolom yang disembunyikan tidak pernah dilewati.
• Kamu butuh kondisi, bukan filter. SUBTOTAL mengikuti apa yang disembunyikan filter. Untuk menjumlahkan North tanpa filter, pakai SUMIF. Untuk ringkasan setiap kelompok sekaligus, pivot table bisa melakukannya tanpa baris total di dalam data.