Fungsi SUM dan Rumus Penjumlahan Pada Excel
Penjelasan Lengkap Rumus Penjumlahan Pada Excel
Ada banyak cara untuk melakukan penjumlahan pada Microsoft Excel. Cara yang paling sederhana adalah dengan menggunakan rumus matematika dengan operator tambah (+). Selain itu juga dapat menggunakan fungsi SUM dan SUBTOTAL untuk menjumlahkan data dalam sekumpulan sel, serta fungsi SUMIF, SUMIFS, SUMPRODUCT dan Rumus Array untuk melakukan penjumlahan bersyarat. Postingan ini mudah-mudahan dapat melengkapi khazanah pemahaman kita mengenai beberapa alternative tehnik dan rumus penjumlahan ms excel.
Okey-lah langsung saja ke pokok pembahasan. Secara garis besar, catatan pelajaran excel kali ini akan membahas:
- Melihat Hasil Penjumlahan Dengan Cepat Memanfaatkan Status Bar
- Penjumlahan Menggunakan Rumus Matematika Sederhana
- Penjumlahan Menggunakan Fungsi SUM
- Cara Menggunakan Autosum di Excel
- Penjumlahan ke Bawah: Menjumlahkan Seluruh Data dalam Kolom Tanpa Pengecualian
- Penjumlahan ke Bawah: Menjumlahkan Seluruh Data dalam Kolom Dengan Pengecualian
- Penjumlahan ke Samping: Menjumlahkan Data Dalam Struktur Baris
- Menjumlahkan Beberapa Kelompok Baris Data
- Penjumlahan ke Samping: Menjumlahkan Seluruh Data Dalam Baris Tanpa Pengecualian
- Menambahkan dan Menggunakan Baris Total pada Data Tabel
- Data Table vs Autofilter: Penjumlahan Data Baris Sel Yang Tampak Saja
- Running Total : Mengkalkulasi Penjumlahan Sampai Dengan Saat Ini
- Cara Penjumlahan Data Antar Sheet (Rumus 3 Dimensi)
- Cara Penjumlahan Bersyarat Dengan Kriteria Tertentu (Kondisional)
- Cara Mengatasi Rumus SUM Yang Error Atau Tidak Bekerja
Mari kita bahas satu persatu:
Cara Cepat Untuk Mengetahui Hasil Penjumlahan Di Excel Dengan Memanfaatkan Status Bar
Sebelum melangkah lebih lanjut, perlu diketahui bahwa sebenarnya kita bisa mengetahui dengan cepat hasil penjumlahan bilangan pada range sel.
Caranya sederhana yaitu cukup dengan menyeleksi range sel dimaksud menggunakan mouse, kemudian perhatikan status bar pada bagian bawah kanan jendela excel.
Disitu kita bisa menemukan keterangan SUM diikuti bilangan yang merupakan hasil penjumlahan data numerik pada kumpulan sel yang diseleksi. Berikut screenshot contohnya :
Perhatikan bahwa status bar tetap memberikan informasi hasil penjumlahan range yang diseleksi meskipun dalam range tersebut ada sel yang berisi text.
Untuk mendapatkan hasil penjumlahan yang bersifat permanen, maka kita harus membuat rumus penjumlahan yang diketikan di sel excel. Ada beberapa tehnik excel seputar penjumlahan. Mari kita explorasi lebih lanjut.
Bagaimana Cara Melakukan Penjumlahan di Excel Menggunakan Rumus Matematika Sederhana
Ini merupakan cara yang paling sederhana untuk melakukan penjumlahan di Excel. Anggaplah seperti memanfaatkan Excel sebagai kalkulator, penjumlahan cukup menggunakan operator tambah (+) seperti normalnya membuat rumus matematika.
Cara pertama : Mengetikan langsung bilangan pada rumus excel
=1+2+3
Cara kedua : Menyimpan bilangan pada sel excel, kemudian membuat rumus penjumlahan dengan menggunakan referensi sel. Contoh berikut merupakan rumus excel untuk menjumlahkan bilangan yang ada pada sel A2, A3, dan A4.
=A2+A3+A4
Perhatikan bahwa penggunaan operator tambah (+) dalam penjumlahan di excel mengharuskan penambahan referensi masing-masing sel satu persatu meskipun sel-sel tersebut posisinya berurutan dan berada dalam satu baris atau kolom di excel yaitu: A2, A3, dan A4.
Jika hanya ada dua atau tiga sel, cara tersebut cukup mudah digunakan untuk mendapatkan hasil penjumlahan dengan cepat. Namun jika data yang akan dijumlahkan berada dalam range yang terdiri atas puluhan, ratusan atau bahkan ribuan sel, maka penggunaan rumus tersebut menjadi tidak efisien.
Solusi yang tepat untuk mengatasi masalah ini adalah beralih ke penggunaan fungsi SUM.
Bagaimana Cara Menggunakan Fungsi SUM di Excel
Fungsi SUM berada dalam grup Math & Trig dan merupakan salah satu fungsi yang paling sering digunakan di excel. Fungsi SUM ini dapat dituliskan dengan Syntax sebagai berikut;
SUM(number1, [number2] ,...)
Perhatikan, sebenarnya fungsi SUM hanya mewajibkan argumen pertama saja yaitu number1. Sedangkan argumen ke-dua dan argumen berikutnya bersifat opsional. Ini berarti fungsi SUM bisa bekerja untuk menjumlahkan meski hanya menggunakan satu referensi berupa range cell. Hal ini sangat berbeda dengan penggunaan operator tambah yang harus memasukan referensi satu persatu atas sel yang nilainya akan dijumlahkan meskipun sel terebut berada dalam range yang kontinyu.
Sebagai info tambahan bahwa excel ternyata membatasi jumlah argumen sampai 255 dalam satu formula SUM. Untungnya, dalam prakteknya sangat jarang kita perlu menggunakan argument sebanyak itu. Bahkan mungkin lebih banyak pengguna excel yang menggunakan satu atau dua argumen saja dalam rumus SUM.
Di dalam formula SUM, masing-masing argument bisa berupa bilangan positif atau negatif, bisa juga berupa range atau referensi sel. Contohnya:
Rumus | Keterangan |
=SUM(10,3,-8) | Rumus SUM menggunakan bilangan yang langsung diketikan sebagai argumen fungsi. |
=SUM(B6,B10,B15) | Rumus SUM untuk menjumlahkan bilangan yang berada di sel B6, B10 dan B15. |
=SUM(B6,B10,B15,3,-10) | Rumus SUM dengan kombinasi bentuk argumen referensi dan nilai langsung, untuk menjumlahkan bilangan yang berada di sel B6, B10 dan B15 dan ditambahkan bilangan 3 dan -10 |
=SUM(B1:B200) | Rumus SUM untuk menjumlahkan semua bilangan yang berada pada range B1:B200, atau data pada kolom B, baris pertama sampai baris 200. Ini sangat berguna untuk menjumlahkan semua nilai pada range kolom yang kontinyu sehingga cukup menggunakan satu referensi saja. |
=SUM(A2:A4,A8:A9) | Rumus SUM untuk menjumlahkan bilangan yang ada pada range A2:A4 dan A8:A9. Ini berguna jika kita ingin menjumlahkan data pada range yang tidak kontinyu. |
=SUM(A2:A4,A8:A9,2,C3) | Rumus SUM dengan kombinasi bentuk argumen referensi range, sel tunggal dan bilangan langsung, yaitu untuk menjumlahkan bilangan pada range A2:A4, A8:A9, bilangan 2 dan bilangan pada sel tunggal C3 |
Dalam pekerjaan nyata, fungsi SUM di excel sering digunakan sebagai salah satu bagian dari formula yang lebih komplek.
Sebagai contoh, kita dapat menyertakan fungsi SUM untuk mengisi argumen value_if_true di dalam fungsi IF untuk menjumlahkan bilangan di kolom B, C dan D jika tiga sel pada baris yang sama masing-masing memiliki value, dan memberikan peringatan jika ada salah satu sel atau lebih yang kosong.
=IF(AND(C3<>"",D3<>"",E3<>""),SUM(C3:E3),"Data Tidak Lengkap")
Bagaimana Cara Melakukan Autosum di Excel – Penjumlahan Cepat Range Secara Vertikal dan Horizontal
Alih-alih mengetikan rumus secara langsung, Excel ternyata sudah memberikan kemudahan untuk membuat rumus SUM secara otomatis menggunakan fasilitas Autosum
Caranya sangat mudah, cukup dengan menyeleksi sel berikutnya setelah sel terakhir dalam range kolom maupun baris untuk menempatkan rumus. Kemudian pergi ke tab HOME, bagian group editing. Dan tekan tombol Autosum, maka otomatis sebuah formula SUM akan disisipkan.
Perhatikan screenshot diatas. Anda dapat mencobanya dengan data serupa. Seleksi sel E9, kemudian tekan tombol autosum (∑). Maka secara otomatis, exel akan menyisipkan fungsi SUM, sekaligus besama argumen berupa referensi sel yang akan dijumlahkan bilangannya. Biasanya excel langsung memberikan referensi yang tepat untuk dijumlahkan jika data kontinyu, tidak terputus oleh sel kosong maupun type data yang berbeda.
Silahkan dicoba menggunakan proses yang sama pada sel D9 dan perhatikan apa yang terjadi. Referensi yang disarankan autosum adalah range D7:D8. Hal ini terjadi karena data terputus oleh adanya sel kosong pada sel D6. Kita dapat mengeditnya dengan menggeret referensi menggunakan mouse.
Silahkan dicoba juga menggunakan proses yang sama pada sel F3 dan sel lainnya pada kolom jumlah dan perhatikan apa yang terjadi. Secara otomatis fungsi SUM akan disisipkan dan mengambil referensi sel di sebelah kiri dan akan dibatasi sel dengan type yang berbeda, sel kosong atau rumus lainya (jika ada)
Tips! Cara yang lebih cepat dan sangat disarankan bagi anda untuk melakukan autosum di excel adalah dengan menggunakan shortcut ALT + = . Cukup tekan tombol ALT di keyboard, kemudian tekan tanda samadengan (=), maka otomatis formula SUM akan disisipkan.
Penjelasan barusan juga dapat menjawab bagaimana cara menjumlahkan data secara vertikal dan horizontal dengan cepat. Selain melakukan kalkulasi total penjumlahan (fungsi SUM), kita juga dapat menggunakan autosum untuk menyisipkan fungsi lainnya secara otomatis, seperti fungsi AVERAGE, COUNT, MAX, dan MIN.
Cara Menjumlahkan Secara Vertikal Semua Data di Kolom Tanpa Pengecualian atau Tanpa Batasan Baris
Penjumlahan data dengan cara ini diperlukan jika banyaknya baris data dalam kolom bersifat dinamis atau berubah terus. Cara membuat rumus untuk kasus ini adalah dengan menggunakan referensi kolom tanpa menggunakan batas baris paling atas atau bawah dalam kolom.
Misalnya: Untuk menjumlahkan semua data numerik di kolom C, kita dapat menggunakan rumus berikut:
=SUM(C:C)
Pehatikan cara penulisan referensi yaitu C:C tanpa angka. Hal ini menandakan bahwa referensi berupa keseluruhan baris dalam kolom C tanpa batas atas maupun bawah.
Penting diperhatikan bahwa dalam kasus ini kita tidak dapat menempatkan rumus SUM pada kolom yang sama dengan referensi. Misalnya ketika kita ingin melakukan penjumlahan data di kolom C, maka jangan menempatkan rumus di kolom C.
Kenapa tidak boleh? Karena hal tersebut akan menimbulkan circular reference dan formula akan menghasilkan bilangan nol. Circular reference ini timbul karena sel menggunakan rumus SUM dengan referensi sel itu sendiri mengakibatkan proses kalkulasi berputar atau terus menerus karena saling mempengaruhi
Pada bagian kiri screenshot diatas menggambarkan bagaimana rumus =SUM(C:C) menghasilkan nilai Nol jika ditempatkan pada sel C8. Hal ini disebabkan circular reference. Sebagai solusinya maka rumus =SUM(C:C) harus ditepatkan di kolom lain misalnya pada sel D8 seperti contoh diatas (yang sebelah kanan)
Menjumlahkan Secara Vertikal Satu Kolom Penuh Dengan Beberapa Pengecualian Baris Tertentu
Membuat rumus yang menjumlahkan data keseluruhan baris dalam sebuah kolom excel sesuai contoh sebelumnya bisa menjadi solusi jika baris data dalam kolom bersifat dinamis dan bertambah atau berubah terus.
Dan pada contoh sebelumnya kita dapat mengetahui bahwa rumus SUM(C:C) akan mengabaikan text header dalam perhitungan, sehingga meskipun header merupakan bagian dari referensi. Rumus tetap dapat bekerja dengan baik dan menghasilan penjumlahan yang benar.
Masalah akan timbul jika header berupa bilangan atau ada baris tertentu yang nilainya tidak ingin dijumlahkan. Maka rumus tersebut akan juga menjumlahkan bilangan yang ada di kolom C meskipun sebenarnya tidak dikehendaki. Hal ini menyebabkan hasil penjumlahan tidak lagi relevan. Maka sebagai solusinya maka kita harus membuat rumus supaya baris tertentu tidak diperhitungkan atau dikeluarkan dari penjumlahan.
Sangat disayangkan, Excel tidak mengizinkan penggunaan batas baris hanya atas atau bawah saja. Excel hanya mengizinkan batas atas dan bawah harus ada keduanya atau tidak ada sama sekali. Makanya penulisan rumus dengan referensi C2:C dalam rumus Excel tidak dapat diterima.
Sebagai solusinya maka dapat dilakukan pengurangan dengan rumus sesuai ilustrasi berikut:
Perhatikan ilustrasi diatas. Pada bagian kiri, nilai yang dihasilkan oleh rumus =SUM(C:C) tidak relevan karena rumus tersebut juga ikut menjumlahkan bilangan pada baris 2 dan 3 yang merupakan target maksimal dan minimal penjualan. Sebagai solusinya maka kita harus menyisipkan rumus tambahan untuk mengurangkan bilangan yang ada di baris 2 dan 3. Sehingga rumus menjadi =SUM(C:C)-SUM(C1:C3)
Selain dengan cara diatas, kita juga dapat secara explisit menggunakan batas baris minimum dan baris maksimum dalam referensi agar baris tertentu tidak diperhitungkan dalam penjumlahan. Ada perbedaan baris maksimun antar excel versi lama (2003 ke bawah) dan excel versi baru (2007 ke atas) yaitu:
Pada Excel 2013 dan sebelumnya: baris maksimum = 65536
Rumus dapat dituliskan =SUM(C4:C65536)
Pada Excel 2007, 2010, 2013 dan 2016:baris maksimum = 1048576
Rumus dapat dituliskan =SUM(C4:C1048576)
Cara Menjumlahkan Data Secara Horizontal (Penjumlahan Ke Samping) di Excel
Sebagaimana penjumlahan data vertikal pada kolom, kita juga dapat menggunakan fungsi SUM atau AutoSum untuk menjumlah data ke samping secara horizontal pada baris.
Sebagai contoh, untuk menambahkan nilai pada sel C3 s.d E3 (pada baris 3) maka dapat digunakan rumus sebagai berikut:
=SUM(C3:E3)
Cara Menjumlahkan Data Pada Beberapa Kelompok Baris di Excel
Untuk menambahkan masing-masing baris secara individual, caranya mudah, cukup dengan drag rumus SUM pada baris pertama dalam tabel. Selain itu bisa juga dengan cara copy paste.
Seandainya diinginkan rumus yang bisa dicopy ke kolom lain dengan tanpa merubah referensi kolom, maka rumus tersebut harus menerapkan tanda dolar $ pada kolom. Perhatikan contoh berikut:
=SUM($B2:$D2)
Untuk menjumlahkan nilai dalam range yang terdiri atas beberapa baris, mudahsaja yaitu dengan cara menggunakan range tersebut dalam formula. Misalnya:
=SUM(C3:E6) - Menjumlahkan bilangan Pada Baris 2 to 6, kolom C s.d E
=SUM(C3:E5, C6:E6) - Menjumlahkan bilangan pada bairis 2,3,5 dan 6 kolom C s.d E
Cara Menjumlahkan Secara Horizontal Seluruh Data dalam Baris Penuh Tanpa Pengecualian
Seperti halnya menjumlahkan data numerik pada kolom penuh, Kita juga bisa menjumlahkan semua data numerik yang ada dalam baris penuh. Caranya adalah dengan menggunakan referensi baris penuh (tanpa menggunakan kolom dalam referensi). Seperti contoh berikut:
=SUM(2:2)
Catatan: Secara praktikal, kita sebenarnya tidak perlu mengetikan referensi secara langsung. Cukup ketikan saja =SUM( kemudian dengan menggunakan mouse pilih baris penuh yang akan dijadikan referensi. Kemudian tekan enter.
Dan perlu diingat juga, bahwa kita tidak bisa menempatkan rumus SUM untuk baris penuh pada sel di baris yang sama dengan referens. Karena hal ini akan menyebabkan circular reference.
Seperti halnya penjumlahan vertikal kolom penuh pada basan di bagian atas, Kita juga dapat memastikan supaya beberapa kolom tertentu tidak ikut terjumlahkan dengan dara mengurangkan penjumlahan baris penuh dengan penjumlahan beberapa sel yang tidak dikehendaki. Sehingga rumus dapat dimodifikasi menjadi seperti berikut:
=SUM(2:2)-SUM(A2:B2)
Bagaimana Cara Menampilkan dan Menggunakan Baris Total pada Pada Tabel
Jika data disusun dalam bentuk tabel (data tabel). maka kita dapat memanfaatkan fitur Total Row yang dapat dengan cepat menjumlahkan data pada tabel dan menampilkan data total pada baris terakhir.
Catatan: jangan dibingungkan dengan istilah Data Table. Sejatinya excel digunakan untuk membuat tabel dan anda bisa mengklaim sudah membuat tabel di exel. Namun tidak semua tabel adalah Data Table dalam pengertian konsepsional di exel. Istilah Data Table mulai diperkenalkan di excel 2007 dan merupakan pengembangan dari excel list pada excel versi sebelumnya.
Manfaat yang besar ketika menggunakan tabel exel adalah bahwa ia dapat melakukan auto expand untuk memasukan baris yang baru, sehingga setiap ada data baru yang diinput dalam tabel akan masuk ke formula secara otomatis. Pelajari lebih lanjut 10 Fitur Paling Bermanfaat di Excel Table.
Untuk mengkonversi range sel menjadi data tabel, seleksi range tersebut kemudian lakukan salah satu cara berikut:
Cara pertama : Tab Insert , kemudian tekan tombol table, dan klik Ok
Cara Kedua : Tekan CTR+T , kemudian tekan Ok.
Cara Menampilkan Total Row Pada Data Tabel
Pilih salah satu sel yang merupakan bagian dari data tabel sehingga ribbon excel menampilkan Table Tools dan Tab Design
Di design Tab, pada grup Table Style Options, seleksi Total Row Box, maka otomatis baris total akan terbentuk sekaligus rumus total pada kolom jumlah yaitu:
Cara lain untuk menambahkan baris total di excel adalah dengan cara klik pada sebarang dalam data tabel, klik kanan, kemudian klik Tabel , dan klik Total Rows.
Perhatikan ilustrasi berikut, cara ini juga dapat digunakan jika anda masih menggunakan excel 2003 pada data list.
Cara Menggunakan Total Row pada Data Tabel
Berikutnya setelah berhasil memunculkan baris total pada data table. Kita dapat melihat bahwa total penjumlahan tercipta secara otomatis pada kolom jumlah saja yaitu pada kolom paling kanan. Untuk mendapatkan nilai penjumlahan kolom lainnya lakukan seleksi dropdown list pada sel bekenaan dan kemudian pilih Sum.
Anggaplah anda bermaksud menjumlah kan data pada kolom Januari, maka anda seleksi sel pada baris total, kolom Januari, yaitu sel C9. Dalam sel tersebut akan menemukan dropdown list. Klik dropdown list tersebut, kemudian pilih Sum. Maka otomatis muncul rumus penjumlahan menggunakan fungsi SUBTOTAL pada sel tersebut.
Dengan cara yang sama, silahkan dicoba pada kolom lainnya untuk mendapatkan total penjualan di bulan Februari dan Maret.
Perhatikan kembali dropdown list pada baris total data tabel. Ternyata selain mendapatkan nilai penjumlahan, kita juga dapat melakukan kalkulasi untuk keperluan lainya seperti mencari nilai rata-rata, menghitung banyaknya data, mendapatkan nilai maksimum dan minimum dan beberapa fungsi lainnya.
Seandainya tidak diperlukan, jumlah total juga dapat dihilangkan kembali dengan cara klik dropdown list pada baris total, kemudian None.
Perlu diingat: Ketika menggunakan fitur total row pada data table excel. Penjumlahan total tersebut hanya mengkalkulasi sel yang tampak saja. Untuk sel yang disembunyikan tidak dikalkulasi. Penjumlahan dengan mengabaikan sel tersembunyi tersebut dikarenakan fitur total row secara otomatis menyisipkan fungsi SUBTOTAL, dengan argumen pertama 109. Silahkan dibaca penjelasan lebih lanjut dalam artikel tentang SUBTOTAL.
Jika anda menginginkan penjumlahan, baik data yang terlihat maupun yang disembunyikan, jangan menggunakan total row, tetapi gunakanlah fungsi SUM
Cara Menjumlahkan Data dalam Sel yang difilter atau yang Tampak Saja
Untuk melakukan analisa data yang lebih efektif, terkadang kita perlu melakukan filter untuk menampilkan dan menyembunyikan data tertentu pada worksheet. Dan terkadang kita juga perlu melakukan kalkulasi pada data terfilter atau data yang kelihatan saja.
Dan untuk kasus ini kita tidak bisa menjumlahkan menggunakan fungsi SUM karena fungsi ini juga akan menjumlahkan data termasuk data pada baris yang disembunyikan.
Ada beberapa cara yang dapat dilakukan untuk filter data di excel diantaranya menggunakan data tabel dan auto fiter.
1. Penjumlahan pada Data Table
Jika anda menginginkan penjumlahan pada visible sel, cara tercepat adalah menggunakan fitur excel Total Row tapi dengan syarat data range harus diconvert terlebih dahulu menjadi data table, sebagaimana sudah dicontohkan pada bagian sebelumnya.
Dengan mengaktivkan Total Row di data tabel, maka otomatis excel akan menyisipkan fungsi SUBTOTAL pada data tabel baris terakhir. Uniknya, fungsi ini akan mengabaikan data pada baris yang di sembunyikan sehingga hanya menjumlahkan data yang tampak saja. Perhatikan ilustrasi berikut:
Contoh ilustrasi diatas menggunakan data dalam bentuk data table sehingga kita dapat menyisipkan fungsi SUBTOTAL tanpa harus mengetikan langsung. Tetapi cukup dengan memilih Sum pada dropdown list (lihat tanda segitiga disamping kanan sel pada baris total) atau dengan menggunakan dengan mengaktifkan Total Row melalui pada Tab Design, kelompok Table Style Option.
Untuk menyisipkan fungsi SUM pada data table bisa dengan cara ketik langsung atau klik dropdown list, kemudian klik More Function… dan pilih SUM
INGAT! Beda antara Sum dengan SUM di Data Table
- Pada saat kita klik Sum di dropdown list maka excel akan menyisipkan rumus SUBTOTAL pada Total Row
- Tapi kalau kita klik More Function… kemudian pilih SUM (huruf kapital semua), maka excel akan menyisipkan rumus SUM pada Total Row.
2. Penjumlahan Dalam Mode Auto Filter
Selain menggunakan Data Tabel, Cara lainnya untuk melakukan filter di excel adalah mengunakan fitur autofilter. Pada excel 2007 ke atas, autofilter dapat dimunculkan dengan menekan shortcut CTR+Shift+L, sedangkan pada excel 2003 (juga semua versi excel yang lebih modern) dengan cara menekan Alt-D-E-FBerbeda dengan Data Table dimana kita dapat menggunakan bantuan dropdown list untuk menyisipkan rumus penjumlahan. Di Autofilter, kita harus mengetikan rumus penjumlahan secara langsung. Karena hasil yang dikehendaki adalah penjumlahan dari range yang tampak saja, maka gunakanlan fungsi SUBTOTAL.
Karena mengetikan rumus secara langsung, maka setidaknya kita harus memahami terlebih dahulu syntax dan cara kerja Fungsi SUBTOTAL. Fungsi ini dapat dituliskan dengan syntax sebagai berikut:
SUBTOTAL(function_num, ref1, [ref2],...)
Dimana Function_num adalah bilangan dari 1 s.d 11 atau dari 101 s.d 111 yang bersifat khusus digunakan untuk subtobal, penjelasan mengenai bilangan tersebut dapat dilihat di support.office.com,
Perbedaan Nomor 9 dan 109 Dalam Fungsi SUBTOTAL:
Untuk sekarang kita hanya tertarik pada nomor fungsi yang didefinisikan dengan bilangan 9 dan 109. Kedua bilangan tersebut sama-sama digunakan untuk memfilter atau mengabaikan data dalam baris tersembunyi dalam proses penjumlahan. Perbedaanya adalah nomor fungsi 109 mengabaikan semua baris tersembunyi termasuk baris yang di-hide secara manual (misalnya dengan right click > hide), sedangkan 9 hanya mengabaikan baris sel yang disembunyikan dalam mode autofilter saja.
Jika diinginkan penjumlahan data pada baris yang kelihatan saja, terlepas dari bagaimana cara baris disembunyikan, baik menggunakan autofilter maupun hide manual, maka 109 adalah nomor yang tepat digunakan sebagai argumen pertama (function_num) dalam rumus SUBTOTAL.
Ref1, Ref2, merupakan range atau sel yang dijadikan sebagai referensi untuk dilakukan operasi SUBTOTAL. Minimal ada satu Referensi dalam fungsi SUBTOTAL, selebihnya excel mengizinkan sampai 254 referensi. Silahkan jika anda mau mencobanya dengan referensi sebanyak itu :-)
Berikut contoh penggunaan fungsi SUBTOTAL untuk menjumlahkan data pada range C3:C10
=SUBTOTAL(109, C3:C10)
Selanjutnya silahkan dicoba filter untuk mengetahui jumlah penjualan jeruk saja:
Perhatikan bahwa baris rumus SUBTOTAL hanya menjumlahkan nilai pada sel yang kelihatan saja, meskipun referensi range C3:C10 mencakup sel yang disembunyikan.
Tips: Menyisipkan Fungsi SUBTOTAL Menggunakan AutoSum
Jika data sudah diorganisir dalam bentuk data tabel atau autofilter, sebenarnya kita dapat menggunakan fitur Autosum untuk menyisipkan rumus SUBTOTAL secara otomatis. Seleksi sel pada baris Total, kemudian pilih salah satu cara berikut:
• Tekan ALT + =
atau
• Dari Tab HOME, tekan tombol autosum ∑
Maka otomatis rumus SUBTOTAL akan disisipkan ke dalam sel.
Jika data diorganisir dalam mode data tabel maka fitur Autosum akan menyisipkan rumus SUBTOTAL dengan argumen function_num 109. Sedangkan jika mode autofilter, maka function_num = 9.
Perhatian! Jika data tidak diorganisir dalam bentuk data table dan autofilter, atau auto filter tetapi belum dilakukan action untuk memfilter, maka secara default autosum akan menyisipkan rumus SUM, bukan SUBTOTAL.
Bagaimana Cara untuk Penjumlahan Data Sampai Dengan Atau Running Total di Excel
Dalam kehidupan nyata, seringkali kita harus menghitung berapa jumlah nilai yang terus bergerak setiap waktu. Misalnya: Berapa penjualan sampai dengan hari ini, atau berapa produksi sampai dengan bulan ini. Di excel, kita dapat mengkalkulasinya menggunakan rumus penjumlahan sampai dengan saat ini atau running total.
Anggap saja, kita ingin mengetahui sudah berapa banyak hasil penjualan buah lengkeng dari awal bulan sampai dengan hari ini. Untuk penjumlahan penjualan sampai dengan di excel tersebut dapat dilakukan dengan beberapa cara, diantaranya:
1. Menggunakan Rumus Matematika Operasi Penambahan
Cara ini adalah dengan menggunakan operator tambah (+) untuk melakukan penjumlahan. Perhatikan screenshot berikut
Tanggal pertama sebagai patokan awal langsung mengabil data dari penjualan tanggal tersebut, rumus =C2
Tanggal ke-2 dan seterusnya menjumlahkan data penjumlahan sampai dengan hari sebelumnya ditambah dengan tanggal berjalan. Ketikan Rumus, =D2+C3 di sel D3, kemudian copy rumus tersebut ke baris berikutnya.
Cara tersebut memiliki kelemahan yaitu jika kita menghapus baris (delete row) atau menghapus salah satu formula di kolom D, maka hasil penjumlahan akan menjadi error atau tidak relevan.
Sebagai solusinya maka disarankan untuk beralih ke cara berikutnya (Menggunakan Fungsi SUM)
2. Menggunakan Fungsi SUM
Cara ini memanfaatkan fungsi SUM dengan menerapkan cara penulisan referensi absolute dan relative secara tepat.Perhatikan contoh dalam screenshot berikut:
Ketikan rumus =SUM(C$2:C2) di sel D2, kemudian copy rumus tersebut ke baris berikutnya.
Perhatikan kembali penggunaan tanda absolute ($) pada referensi rumus. Ini digunakan untuk mengunci referensi baris 2 sehingga ketika dicopy ke baris berikutnya, maka rumus akan tetap menghitung mulai dari baris ke-2 sampai ke baris berjalan.
Cara penggunaan referensi absolute dan relative dalam rumus excel dijelaskan dalam artikel lainnya.
Rumus 3 Dimensi : Bagaimana Cara Melakukan Penjumlahan Antar Sheet
Selain menggunakan operator penambahan, kita juga dapat melakukan penjumlahan antar sheet menggunakan fungsi SUM. Jika layout dan type data beberapa sheet tersebut sama maka kita dapat melakkan penjumlahan antar sheet menggunakan fungsi SUM secara mudah dan singkat.Misalnya untuk menjumlahkan data pada beberapa sheet dengan nama Jan s.d Des, dapat digunakan rumus SUM secara singkat yaitu
=SUM(Jan:Des!C2)
Rumus tersebut digunakan untuk menjumlahkan sel C2 yang terletak pada semua worksheet mulai dari "Jan" s.d "Des". Perhatikan bahwa worksheet yang terletak antara "Jan" s.d "Des" tidak perlu direferensikan satu persatu karena sudah terwakili dengan referensi "Jan:Des". Inilah yang dimaksud dengan referensi 3 Dimensi”
Penjumlahan Bersyarat Dengan Kriteria / Kondisi Tertentu
Penjumlahan dengan kriteria atau penjumlahan yang mensyaratkan dengan kondisi tertentu sering diperlukan dalam pekerjaan sehari-hari. Contohnya :
• Satu kriteria: Menghitung jumlah penjualan produk tertentu
• Dua kriteria: Menghitung jumlah penjualan produk tertentu terhadap konsumen tertentu.
• Tiga kriteria: Menghitung jumlah penjualan produk tertentu terhadap konsumen tertentu di waktu tertentu.
Contoh praktikal lainnya misalnya untuk menjumlahkan berdasarkan nama, menjumlahkan berdasarkan tanggal, menjumlahkan berdasarkan bulan, dan menjumlahkan dengan kriteria lainnya.
Ada Beberapa cara yang dapat digunakan untuk melakukan penjumlahan bersyarat di excel:
Penjumlahan Bersyarat Menggunakan Fungsi SUMIF dan SUMIFS
Fungsi SUMIF digunakan untuk menjumlahkan dengan satu kondisi, sedangkan SUMIFS bisa melakukan penjumlahan dengan beberapa kondisi. Hanya saja, fungsi SUMIFS tersedia mulai excel versi 2007 ke atas.Perhatikan contoh dalam scrheenshot berikut:
Dalam tabel tersebut, data produk terdapat pada range B3:B10, data Buyer pada range C3:C10, dan data jumlah penjualan pada range D3:D10
Untuk mendapatkan total penjualan buah Durian maka dapat dituliskan rumus sebagai berikut.
=SUMIF(B3:B10,"Durian",D3:D10)
Karena Syarat “Durian” disimpan di sel B14, maka rumusnya menjadi
=SUMIF(B3:B10,B15,D3:D10)
Selain itu, dapat juga digunakan rumus SUMIFS untuk penjumlahan bersyarat satu kriteria.
=SUMIFS(D3:D10,B3:B10,"Durian")
Fungsi SUMIFS juga dapat digunakan untuk penjumlahan bersyarat dengan dua kriteria atau lebih, misalnya untuk mendapatkan jumlah penjualan “Durian” dengan buyer “Asep”:
=SUMIFS(D3:D10,B3:B10,"Durian",C3:C10,"Asep")
Penjelasan lebih detail tentang penggunaan fungsi SUMIF dan SUMIFS dapat dilihat di artikel lainnya.
Selain SUMIF dan SUMIFS, masih ada alternative cara penjumlahan bersyarat yaitu menggunakan fungsi SUMPRODUCT dan rumus Array.
Penjumlahan bersyarat menggunakan fungsi SUMPRODUCT
Mendapatkan total penjualan buah “Durian” (1 kriteria)=SUMPRODUCT((B3:B10="Durian")*(D3:D10))
Mendapatkan total penjualan buah “Durian”, dengan buyer “Asep” (2 kriteria)
=SUMPRODUCT((B3:B10="Durian")*(C3:C10="Asep")*(D3:D10))
Penjumlahan Bersyarat Menggunakan Rumus Array
Setelah mengetikan rumus array, jangan lupa untuk tekan CTR+SHIFT+ENTERMendapatkan total penjualan buah “Durian” (1 kriteria)
=SUM((B3:B10=B22)*(D3:D10))
Mendapatkan total penjualan buah “Durian”, dengan buyer “Asep” (2 kriteria)
=SUM((B3:B10="Durian")*(C3:C10="Asep")*(D3:D10))
Rumus SUM Bermasalah, Error, Tidak Berfungsi atau Tidak Bekerja – Penjelasan Penyebab dan Solusinya.
Kemungkinan besar anda pernah mengalami masalah seputar penggunaan rumus penjumlahan, khususnya penjumlahan menggunakan fungsi SUM.
Berikut beberapa permasalahan fungsi SUM, penjelasan penyebab dan solusi untuk mengatasinya:
1. Rumus menghasilkan error #NAME
Error ini terjadi terutama disebabkan kesalahan pengetikan rumus SUM atau menggunakan nama referensi yang salah.Misalnya, tanpa disengaja menuliskan rumus SUN, padahal seharusnya SUM
Error #NAME juga dapat disebabkan penggunakan nama referensi yang salah atau belum didefinisikan.
=SUM(range_jumlah)
Dalam contoh diatas, jika tidak ada range dengan nama range_jumlah maka rumus tersebut akan menghasilkan error #NAME.
Solusi permasalahan error jenis ini adalah dengan memeriksa apakah fungsi dan nama referensi sudah diketikan dengan benar. Jika dirasa sudah benar namun masih error, maka periksa kembali nama referensi apakah sudah didefininikan atau tidak. Cara penamaan range referensi dapat dibaca pada artikel lainnya: Cara Memberi Nama Range Cell Menggunakan Name Box
2. Beberapa bilangan tidak terjumlahkan
Salah satu penyebab umum mengapa rumus SUM tidak mendapatkan hasil yang relevan adalah karena adanya bilangan yang yang ditulis sebagai text mengguakan tanda apstrope (tanda petik satu) di depannya . Secara visual, mungkin akan nampak sebagai bilangan, tapi kenyataannya bukan karena excel membaca data tersebut sebagai text sehingga tidak bisa dikalkulasi atau dijumlahkan.
Untuk mengetahui bahwa bilangan terbaca sebagai text oleh Excel dapat ditandai dengan adanya indikator berupa tanda segitiga hijau kecil pada pojok kiri atas sel, dan kalau kita seleksi sel tersebut dengan kursor maka akan muncul pemberitahuan “The number in this cells is formated as text or preceded by an apostrophe”
Masalah ini dapat diatasi dengan mengkonversi bilangan yang diformat text menjadi bilangan nomal. Caranya seleksi sel yang mengandung bilangan yang diformat text, kemudian klik tanda peringatan dan selanjutnya klik “Convert To Number”
3. Rumus SUM menghasilkan nilai 0, padahal Format Bilangan sudah benar.
Selain karena bilangan yang diformat sebagai text, sircular reference juga merupakan penyebab umum rumus SUM tidak berfungsi, terutama ketka kita mencoba mentotalkan data bilangan di kolom excel. Sehingga jika anda sudah memastikan bilangan diformat bilangan (bukan text) tetapi hasil rumus masih belum benar, maka kemudian tuntaskan masalahnya di circular reference. Penjelasan lengkapnya bisa dibaca di artikel: Rumus Excel Tidak Berfungsi, Inilah Penyebabnya:4. Rumus SUM menghasilkan nilai yang lebih besar dari seharusnya.
Jika kemudian semua hal di atas sudah diatasi, namun formula masih belum menghasilkan nilai yang sesuai. Coba cek barangkali ada sel yang di sembunyikan (hidden cells). Karena fungsi sum akan menjumlahkan termasuk bilangan pada sel yang disembunyikan.Jika ada sel yang disembunyikan dan kita tidak mengharapkan penjumlahan dari sel yang disembunyikan tersebut, maka cobalah menggunakan fungsi SUBTOTAL, seperti sudah dijelaskan pada bagian atas.
5. Hasil Rumus SUM tidak Update Atau Tidak Mengkalkulasi Otomatis
Ketika sebuah rumus SUM di excel tidak berubah pada saat kita merubah nilai di salah satu parameternya, atau pada saat kita mengcopy rumus SUM ke sel lain tetapi hasil rumus tidak berubah. Ini dapat disimpulkan bahwa mode kalkulasi di set manual. Untuk mengatasi ini, pergi ke tab formula, kemudian klik calculate option dan klik automatic.Penjelasan lebih lengkap perihal cara setting kalkulasi dapat dibaca pada Artikel yang membahas Penyebab Rumus Excel Tidak Berfungsi
Bailkah, kelima hal di atas adalah penyebab umum mengapa rumus SUM tidak bekerja dengan benar di excel. Dengan mengetahui penyebab masalah rumus error atau tidak dapat bekerja dengan benar, maka diharapkan kita bisa mendapatkan solusi yang tepat untuk mengatasinya.
……….
Akhir kata, demikian pembahasan bagaimana melakukan penjumlahan pada excel dengan menggunakan fungsi SUM dan alternatif fungsi penjumlahan lainnya. Juga bagaimana cara menggunakan fitur autosum, data tabel, autofilter, serta melakukan penjumlahan bersyarat dan mengatasi error seputar rumus penjumlahan.
Mudah-mudahan bermanfaat
Salam
Artikel Terkait:
Rumus Excel Tidak Berfungsi? Inilah Penyebabnya
Cara Mudah Mempelajari Fungsi dan Formula Exel
Penerapan Fungsi IF Pada Rumus Excel
VLOOKUP-Rumus Jitu Yang Wajib Dikuasai
Referensi:
https://www.ablebits.com/office-addins-blog/2016/05/18/excel-sum-formula-total-column-rows-cells/
https://support.office.com/en-US/article/SUM-function-043E1C7D-7726-4E80-8F32-07B23E057F89
https://support.office.com/en-US/article/SUBTOTAL-function-7B027003-F060-4ADE-9040-E478765B9939
Sertifikat Komputer dan TOEFL tanpa ikut kursus, Ikuti Paket Ujian Saja
Klik Disini
Posting Komentar