Rumus SUBTOTAL Excel, Aturan Penulisan + Contoh Lengkap

Rumus SUBTOTAL Excel termasuk kedalam kategori Fungsi Math & Trig yang berguna untuk menghitung subtotal dari sebuah daftar atau tabel data.

Maksud menghitung subtotal seperti ini: Rumus SUBTOTAL hanya akan menghitung nilai pada cell yang terlihat saja dan mengabaikan nilai dari cell yang disembunyikan (hidden cell) menggunakan fitur Filter maupun secara manual.

Sebelum belajar cara kerja dan cara menggunakan Rumus SUBTOTAL Excel, ada baiknya pahami dahulu apa yang akan Anda dapatkan ketika menggunakan Rumus ini.

Mulailah dari bagian pertama: Perbedaan SUBTOTAL dengan SUM, AVERAGE dll…

Apa Perbedaan SUBTOTAL dengan SUM, AVERAGE dll. ?

Dalam penerapannya, Rumus SUBTOTAL dapat menggantikan 11 fungsi Excel lainnya yaitu: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, VAR.P.

Penting! Sebelumnya Saya sudah membahas tentang penggunaan Rumus COUNT & SUM dkk (lihat Pintasan Panduan).

Saya yakin Anda sudah tahu ini: Rumus SUM, AVERAGE dll pasti akan menyertakan cell yang tersembunyi kedalam perhitungan.

Sementara Rumus SUBTOTAL akan mengabaikannya. Misalnya seperti ini…

Saat ini tidak ada baris yang disembunyikan.

Selanjutnya, coba sembunyikan baris 4 dan 5 menggunakan Fitur AutoFilter atau sembunyikan secara manual. Dan lihat hasilnya:

Penting! Saya juga menulis panduan lengkap tentang penggunaan fitur AutoFilter Excel dan cara menyembunyikan cell secara manual, selengkapnya silahkan kunjungi SUB-BAB Filter & AutoFilter dan SUB-BAB Hide Columns & Rows.

Hasilnya beda kan ? Ada perbedaan hasil dari rumus SUM dan AVERAGE dengan Rumus SUBTOTAL.

Seperti hasil SUBTOTAL SUM pada Cell C₁₀ yaitu 20. Ini hasil dari 2+8+10.

Begitu juga dengan SUBTOTAL AVERAGE pada cell C₁₂. Ini hasil dari (2+8+10)/3 = 20/3 = 6.6667.

Namun, ada sedikit aturan dalam penggunaan Rumus SUBTOTAL Excel. Jadi pahami dulu aturannya ya…

Aturan Penulisan Rumus SUBTOTAL Excel

Sama seperti Rumus Excel lainnya, Rumus SUBTOTAL juga memiliki aturan penulisan tersendiri yang terdiri dari beberapa argumen seperti berikut:

Tulis Rumus / Fungsi SUBTOTAL:
=SUBTOTAL(function_num,ref1, …)

1. function_num (wajib) merupakan kode berupa angka dari 1 sampai 11 atau 101 sampai 111 yang akan menentukan Fungsi mana yang digunakan dalam rumus SUBTOTAL.
2. ref1 (wajib) merupakan satu atau beberapa cell, range, ataupun sebuah rumus yang akan dihitung subtotalnya.
3. ... (optional) merupakan argumen optional yang memiliki makna sama dengan argumen ref1. Argumen ini berguna jika Anda memiliki data terpisah yang akan dihitung subtotalnya.

Jika Anda sudah paham argumen dalam rumus SUM, AVERAGE dll, tentu tidak akan asing lagi dengan argumen ref1 dan ... dalam rumus SUBTOTAL ini.

Jadi, argumen ref1 dan ... pada rumus =SUBTOTAL(function_num,ref1,...) memiliki arti yang sama dengan argumen =SUM(number1,[number2],...).

Cara Menghitung SUBTOTAL di Excel

Penting! Sebelum melanjutkan, ketahui bahwa operator pemisah rumus untuk Software Excel format English berbeda dengan format Indonesia ( , atau ; ). Software Excel Saya menggunakan Format English. Jika masih ragu, silahkan kunjungi SUB-BAB Format English vs Indonesia

Untuk menghitung SUBTOTAL, silahkan ikuti tahap-tahap berikut:

1. Ketik rumus SUBTOTAL seperti berikut: =SUBTOTAL(
2. Masukkan argumen function_num. Dalam contoh ini Saya menggunakan 109 untuk SUM. Kemudian ketik operator pemisah rumus ( , atau ; ). Note: Jangan bingung tentang kode 9 dan 109 untuk SUM. Nanti akan Saya jelaskan.
3. Masukkan argumen ref1. Dalam contoh ini adalah Range C₃:C₇.
4. Jika masih ada data yang akan dihitung, maka masukkan argumen ref2 dan seterusnya dengan cara yang sama. Jika tidak ada, ketik tanda tutup kurung dan tekan Enter. Lihat hasilnya:

Selanjutnya, coba Kita buktikan…

Silahkan gunakan Fitur AutoFilter, kemudian sembunyikan Row 4 dan 5. Ini hasilnya:

Mana Yang terbaik: Function Num 1-11 atau 101-111 ?

Pada dasarnya, menyembunyikan cell bisa dengan 2 cara yaitu menggunakan fitur AutoFilter dan Manual.

Jika Anda perhatikan, Function Num 1 dan 101 sama-sama untuk Fungsi AVERAGE, function num 9 dan 109 untuk Rumus SUM, begitu juga dengan function num lainnya.

Namun, kemampuan function num 1-11 berbeda dengan 101-111. Perhatikan tabel berikut:

Function Num	Fungsi	AutoFilter	Manual
1	AVERAGE	✔	–
2	COUNT	✔	–
3	COUNTA	✔	–
4	MAX	✔	–
5	MIN	✔	–
6	PRODUCT	✔	–
7	STDEV.S	✔	–
8	STDEV.P	✔	–
9	SUM	✔	–
10	VAR.S	✔	–
11	VAR.P	✔	–
101	AVERAGE	✔	✔
102	COUNT	✔	✔
103	COUNTA	✔	✔
104	MAX	✔	✔
105	MIN	✔	✔
106	PRODUCT	✔	✔
107	STDEV.S	✔	✔
108	STDEV.P	✔	✔
109	SUM	✔	✔
110	VAR.S	✔	✔
111	VAR.P	✔	✔

Jadi, Function Num 1-11 hanya mengabaikan baris yang difilter menggunakan fitur AutoFilter.

Sedangkan fungtion num 101-111 akan mengabaikan baris yang difilter menggunakan fitur AutoFilter serta mengabaikan baris yang disembunyikan secara manual.

Bukti Perbedaan Hasil Function Num 1-11 dan 101-111

Misalnya, Saya menggunakan contoh berikut:

Note: Saya menggunakan function num 9 dan 1 pada cell B₁₃ dan B₁₄. Sedangkan cell C₁₃ dan C₁₄ menggunakan 109 dan 101.

Selanjutnya, filter data menggunakan fitur AutoFilter kolom nama (B) dan sembunyikan Nama Dedek dan Ade. Ini hasilnya:

Sejauh ini, function num 1-11 dan 101-111 memberikan hasil yang sama.

Penting! Saya menggunakan bantuan Rumus IF ini =IF(C13=B13,"Sama","Beda") pada Cell D₁₃ dan =IF(C14=B14,"Sama","Beda") pada Cell D₁₄ untuk menguji apakah kedua cell tersebut memiliki nilai yang sama. Pelajari selengkapnya tentang Rumus IF pada SUB-BAB IF Function Lengkap.

Selanjutnya, hilangkan AutoFilter dan coba Sembunyikan Baris 4 dan 5 Secara Manual. Lihat hasilnya:

Hasilnya beda kan ?

Hasil Function Num 1-11 tidak mengabaikan cell tersembunyi (manual). Hasilnya sama saja dengan Rumus SUM dan AVERAGE biasa pada Cell B₉ dan B₁₀.

Sebaliknya function num 101-111 mengabaikan cell tersembunyi (manual).

Penting! Jika Saya harus memilih, maka Saya akan menggunakan function num 101-111. Karena secara default, Excel juga menggunakan function num ini untuk Format Tabel Bernama.

Saya rasa tidak sulit memahami materi Rumus SUBTOTAL Excel ini.

Jadi jika Anda menemukan kendala atau menemukan trik lain, silahkan coret-coret kolom komentar.

Selanjutnya, silahkan pelajari materi menarik lainnya tentang COUNT & SUM Excel melalui Pintasan Panduan Berikut: