Cara Menggunakan Rumus SUBTOTAL Excel: Fungsi, Contoh, dan Langkah Penulisan

Di tutorial ini, kamu akan mempelajari bagaimana cara menggunakan rumus SUBTOTAL di excel.

Ketika bekerja di excel, terkadang kita perlu memproses data dari cell range yang sudah kita filter. Dengan SUBTOTAL, kamu akan bisa melakukan hal tersebut secara jauh lebih mudah.

Mau tahu lebih jauh mengenai rumus SUBTOTAL ini dan bagaimana cara menggunakannya secara optimal untuk mendukung pekerjaanmu di excel? Mari segera kita bahas.

Disclaimer: Artikel ini mungkin mengandung link afiliasi dari mana kami akan mendapatkan komisi untuk setiap transaksi/aksi terkualifikasi tanpa adanya biaya tambahan bagimu. Pelajari lebih lanjut

Ingin bekerja dengan lebih cepat dan mudah di Excel? Instal dan gunakan add-in Excel! Baca artikel ini untuk mengetahui add-in Excel terbaik yang bisa kamu gunakan menurut kami!

Rumus SUBTOTAL Excel Adalah…

SUBTOTAL adalah rumus yang bisa kamu gunakan untuk mengolah data yang lolos filter di excel.

Fungsi SUBTOTAL pada Excel

Kamu bisa menggunakan SUBTOTAL untuk mendapatkan hasil aplikasi dari sejumlah rumus pada data yang ada di cell range yang terfilter. Rumus yang bisa kamu aplikasikan dengan menggunakan SUBTOTAL adalah AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, dan VARP.

Hasil SUBTOTAL

Hasil dari rumus SUBTOTAL adalah hasil pemrosesan data yang lolos filter. Seperti apa pemrosesan data yang akan dilakukannya tergantung dari pilihan rumusmu.

Versi Excel dari Mana Kita Mulai Bisa Menggunakan SUBTOTAL

Kamu bisa menggunakan SUBTOTAL sejak excel 2003.

Cara Penulisan dan Input

Berikut pola penulisan dari rumus SUBTOTAL di excel.



Dan berikut penjelasan dari masing-masing input yang dibutuhkan oleh rumus SUBTOTAL.

• kode_rumus = Kode angka yang merepresentasikan rumus yang ingin kamu aplikasikan pada data lolos filtermu
• cell_range_terfilter1, … = semua cell range dari data lolos filter yang ingin kamu olah dengan menggunakan rumus ini, dipisahkan dengan tanda koma ( , )

Kode Rumus SUBTOTAL

Ketika menuliskan rumus SUBTOTAL di excel, kamu perlu memasukkan kode rumus sebagai input pertama dari rumus ini. Berikut opsi-opsi kode rumus SUBTOTAL yang bisa kamu masukkan inputnya beserta rumus yang masing-masing dari mereka wakili.

Rumus	Memperhitungkan Cell-Cell yang Disembunyikan Secara Manual	Mengabaikan Cell-Cell yang Disembunyikan Secara Manual
AVERAGE	1	101
COUNT	2	102
COUNTA	3	103
MAX	4	104
MIN	5	105
PRODUCT	6	106
STDEV	7	107
STDEVP	8	108
SUM	9	109
VAR	10	110
VARP	11	111

Seperti yang bisa kamu lihat di atas, ada dua jenis kode rumus yang bisa kamu gunakan di SUBTOTAL. Jika kamu ingin memperhitungkan data di cell-cell yang kamu sembunyikan secara manual di luar cell range terfiltermu (bukan cell-cell yang disembunyikan secara otomatis oleh fitur filtel excel ataupun disembunyikan secara manual di cell range yang terfilter tersebut), gunakan kode rumus dari 1 sampai 11. Jika kamu ingin mengabaikan mereka, gunakan kode rumus dari 101 sampai 111.

Contoh Penggunaan dan Hasil

Berikut contoh implementasi rumus SUBTOTAL di excel.



Seperti yang kamu bisa lihat di atas, kita menggunakan 9 sebagai kode rumus SUBTOTAL kita di sini. Sebagai hasilnya, ia akan mengaplikasikan SUM ke angka-angka penjualan di cell range kita yang terfilter. Hasil dari SUMnya di sini adalah 44176.

SUBTOTAL tidak memperhitungkan angka-angka penjualan yang tidak lolos filter. Akan tetapi, karena kita menggunakan 9, bukan 109, ia akan memperhitungkan angka-angka penjualan di baris-baris yang kita sembunyikan secara manual di luar dari cell range yang kita filter. Jika tidak ada baris tersembunyi seperti itu di input SUBTOTAL kita, kita akan mendapatkan hasil SUM yang sama dengan kode rumus 9 ataupun 109.

SUBTOTAL dapat menampung input sampai dengan 254 cell range. Akan tetapi, kita biasanya hanya perlu memasukkan satu input cell range.

Langkah Penulisan

Berikut langkah-langkah untuk menulis rumus SUBTOTAL di excel secara lengkap.

1. Ketik tanda sama dengan ( = ) di cell di mana kamu ingin menaruh hasil SUBTOTALnya
   
   
   
   
2. Ketik SUBTOTAL (boleh dengan huruf besar atau huruf kecil) dan tanda buka kurung setelah =
   
   
   
   
3. Masukkan input kode rumus yang ingin kamu gunakan (untuk referensi kode rumusnya, silahkan lihat kembali tabel di atas) setelah tanda buka kurung. Lalu, ketik tanda koma
   
   
   
   
4. Masukkan cell range terfilter yang datanya ingin kamu olah dengan menggunakan SUBTOTAL. Jika kamu ingin memasukkan beberapa input cell range, pisahkan mereka dengan tanda koma
   
   
   
   
5. Ketik tanda tutup kurung
   
   
   
   
6. Tekan Enter
7. Selesai!
   
   
   
   

SUBTOTAL dengan Kriteria (SUBTOTAL IF)

Mau mengaplikasikan kriteria ke data terfilter yang kamu proses dengan menggunakan SUBTOTAL?

Sayangnya, tidak ada rumus SUBTOTALIF seperti SUM dengan SUMIF dan SUMIFSnya ataupun COUNT dengan COUNTIF dan COUNTIFSnya. Akan tetapi, kita bisa menghasilkan hasil yang kurang lebih sama dengan mengkombinasikan rumus SUBTOTAL dengan SUMPRODUCT, OFFSET, ROW, dan MIN.

Berikut format penulisan umum dari kombinasi kelima rumus tersebut untuk menambahkan kriteria di pemrosesan data kita dengan menggunakan SUBTOTAL.



Dalam kombinasi rumus di atas, kita menggunakan bentuk rumus array (seperti yang kamu bisa lihat dari penggunaan tanda kurung keriting yang melingkupi rumusnya) karena kita memproses data berbentuk array di sini. Oleh karena itu, kita perlu menekan tombol Ctrl + Shift + Enter setelah kita menuliskan rumusnya.

Di rumus ini, SUMPRODUCT membantu rumusnya mempertimbangkan kriteria yang kita punya untuk cell range terfilter kita. Di lain pihak, kombinasi OFFSET, ROW, dan MIN membantu SUBTOTAL memberikan hasil berbentuk array yang bisa diproses bersama dengan hasil dari SUMPRODUCT untuk membuat kita mendapatkan hasil akhir yang kita inginkan (hasil SUBTOTAL yang mempertimbangkan kriteria data).

Perlu diingat bahwa penulisan rumus ini sepertinya hanya bisa bekerja untuk varian rumus COUNT, COUNTA, dan SUM dari SUBTOTAL.

Masih belum bisa memahami rumusnya setelah membaca penjelasan di atas? Mari kita lihat contoh implementasinya berikut ini.



Seperti yang bisa kamu lihat di contoh tersebut, kita bisa menambahkan kriteria di SUM dari SUBTOTAL kita (kuantitas penjualan lebih dari 8000) dengan menggunakan kombinasi rumus SUMPRODUCT, SUBTOTAL, OFFSET, ROW, dan MIN. Sebagai hasil kombinasinya, data yang tidak sesuai dengan kriteria kita diabaikan sehingga kita mendapatkan 25188 sebagai hasil dari SUBTOTAL kita.

Untuk memahami seperti apa proses yang terjadi di dalam rumusnya, kita akan melihat bagian pertama dari rumus SUMPRODUCT kita, yang mana adalah cell range terfilter yang ingin kita evaluasi dengan menggunakan kriteria kita.

Karena kita ingin memfilter cell range yang diproses oleh SUBTOTAL berdasarkan kuantitas penjualannya, kita masukkan input cell range kolom kuantitas penjualan di sini (C3:C14). Kita juga menambahkan kriteria kita setelah input cell rangenya tersebut (>8000) sehingga ia bisa mengevaluasi setiap data yang ada di cell rangenya tersebut.

Evaluasi tersebut menghasilkan nilai-nilai logika TRUE dan FALSE. Oleh karena itu, hasil dari bagian pertama rumus SUMPRODUCT kita adalah array TRUE/FALSE seperti yang terlihat di bawah ini.



Mengapa ada 12 TRUE/FALSE di sini, bukan enam seperti banyak kuantitas penjualan yang bisa kita lihat pada screenshot di atas? Hal ini karena terdapat 12 kuantitas penjualan sebelum kita memfilter tabel kuantitas penjualan kita seperti yang bisa kamu lihat di sini.



Ini juga berarti bagian pertama dari rumus SUMPRODUCT kita tidak mengabaikan data yang sudah terfilter. Untuk membantu kita dalam hal ini, kita gunakan bagian kedua dari rumus SUMPRODUCT kita, yang mana mengandung rumus SUBTOTAL, untuk membantu kita.

Di rumus SUBTOTAL tersebut, kita menggunakan 9 sebagai kode rumus kita karena kita ingin menjumlahkan kuantitas penjualan terfilter kita (kita juga bisa menggunakan 109 karena kita tidak memasukkan input cell range yang disembunyikan secara manual di luar cell range terfilter kita). Karena SUBTOTAL memberikan hasil tunggal, bukan hasil berupa array yang kita butuhkan untuk melakukan perkalian dengan hasil bagian pertama dari rumus SUMPRODUCT kita, kita gunakan rumus OFFSET untuk membantu kita mendapatkan hasil berbentuk array.

Untuk input referensi cell dari rumus OFFSETnya (input pertama), kita masukkan input cell pertama dari kolom kuantitas penjualan kita. Hal ini karena nantinya kita ingin mendapatkan hasil SUBTOTAL dari cell pertama tersebut sampai ke cell yang terakhir dari kolom kuantitas penjualannya. Untuk membuat OFFSET “meregang” sampai ke cell terakhir dari kolom kuantitas penjualan terfilternya tersebut, kita kombinasikan ROW dan MIN untuk input kedua dari rumus OFFSET kita.

Pada contoh, rumus ROW(C3:C14) kita memberikan hasil array seperti ini.



Array tersebut berisi angka-angka baris dari semua cell di dalam kolom kuantitas penjualan terfilter kita. Untuk rumus MIN(ROW(C3:C14)) kita, hasil arraynya adalah seperti ini.



Mengapa kita membutuhkan array berisi 12 angka 3 ini dari hasil kombinasi MIN dan ROW kita? Hal ini karena kita ingin mengurangi array sebelumnya yang dihasilkan oleh rumus ROW kita dengan array ini agar kita bisa mendapatkan array yang tepat untuk “meregangkan” cell kuantitas penjualan pertama kita dengan menggunakan OFFSET.

Hasil dari pengurangan dua array tersebut adalah array seperti ini.



Array ini membuat OFFSET kita berpindah dari cell kuantitas penjualan pertama kita beberapa kali berdasarkan arahan dari angka-angka yang ada di array tersebut. Hasilnya adalah sesuatu seperti ini.



Karena SUBTOTAL kita memproses setiap anggota dari array tersebut, ia akan menghasilkan sesuatu seperti ini.



Angka nol yang ada di array tersebut adalah karena SUBTOTAL mengabaikan cell-cell tersembunyi di kolom kuantitas penjualan terfilter kita.

Sekarang rumus kita tinggal mengalikan hasil dari bagian pertama SUMPRODUCT kita dengan hasil bagian keduanya tersebut. Karena TRUE sama dengan 1 dan FALSE sama dengan 0 di excel, hasil dari perkaliannya akan menjadi array seperti ini.



Setelah dijumlahkan oleh SUMPRODUCT, hasilnya menjadi total kuantitas penjualan yang kita ingin hitung di sini (total kuantitas penjualan untuk kuantitas penjualan terfiliter yang nilainya di atas 8000), yaitu 25188!

Mengaplikasikan Penjumlahan SUBTOTAL Secara Cepat pada Deret Angka Terfilter: AutoSum

Perlu menjumlahkan suatu deret angka lolos filter secara cepat dengan menggunakan SUBTOTAL? Untuk melakukan hal tersebut, kamu bisa menggunakan fitur AutoSum di excel. AutoSum adalah fitur yang bisa membantu menghasilkan rumus SUM untuk menjumlahkan suatu deret angka secara cepat dengan hanya mengklik satu tombol atau menekan tombol shortcutnya. Jika AutoSum mendeteksi bahwa deret angka yang ingin kamu jumlahkan berada dalam cell range terfilter, ia akan menghasilkan rumus SUBTOTAL, bukan SUM.

Untuk menggunakan AutoSum untuk menghasilkan rumus SUBTOTAL secara cepat, pilih satu cell persis setelah deret angka di cell range terfiltermu seperti ini.

Lalu, pergi ke tab Home atau Formulas di ribbon excelmu dan klik tombol AutoSum. Alternatifnya, kamu juga bisa menekan tombol Alt dan = secara bersamaan di keyboardmu (Command + Shift + T di Mac).



Dengan melakukan hal tersebut, rumus SUBTOTAL yang menjumlahkan angka-angka lolos filtermu akan dibuatkan secara otomatis untukmu!

Latihan

Setelah kamu mempelajari bagaimana cara menggunakan rumus SUBTOTAL di excel, kamu bisa mencoba mempraktekkan pemahamanmu dengan mengerjakan latihan berikut!

Unduh file latihannya dari link berikut ini dan jawab pertanyaan-pertanyaannya. Unduh file kunci jawabannya jika kamu sudah selesai menjawab semua pertanyaannya dan ingin mengecek jawabanmu!

Link file latihan:
Unduh di sini

Pertanyaan

1. Berapa nilai maksimum dari kolom C jika kolom A berisi huruf A saja dan kolom B berisi huruf A atau B?
2. Berapa rata-rata nilai dari kolom C jika kolom A berisi huruf A saja dan kolom B berisi huruf A atau B?
3. Berapa jumlah nilai dari kolom C jika kolom A berisi huruf A saja dan kolom B berisi huruf A atau B?

Link file kunci jawaban:
Unduh di sini

Catatan Tambahan

• SUBTOTAL akan mengabaikan hasil rumus SUBTOTAL lain di dalam cell range terfilter yang ia proses
• SUBTOTAL didesain untuk bekerja di data yang tersusun secara vertikal. Jika datanya tersusun secara horizontal, maka cell-cell tersembunyi di dalamnya tidak akan terabaikan oleh SUBTOTAL

Tutorial terkait yang sebaiknya kamu pelajari juga: