Mahir Fungsi Excel: SUM, SUMIF dan SUMIFS

Dalam pengolahan data menggunakan Microsoft Excel, fungsi-fungsi seperti SUM, SUMIF, dan SUMIFS merupakan fungsi penting untuk melakukan perhitungan secara otomatis dan efisien. Ketiga fungsi ini digunakan untuk menjumlahkan data, namun masing-masing memiliki karakteristik dan kegunaan yang berbeda.

SUM digunakan untuk menjumlahkan seluruh nilai dalam suatu rentang, SUMIF digunakan ketika penjumlahan hanya ingin dilakukan untuk satu kriteria tertentu, sedangkan SUMIFS memungkinkan kita menjumlahkan data berdasarkan beberapa kriteria sekaligus.

Pada tutorial ini, kita akan membahas secara lengkap cara penggunaan ketiga fungsi tersebut, tentunya dengan contoh dan ilustrasi yang akan memudahkan pemahaman pembaca semuanya.

Fungsi SUM adalah fungsi paling dasar untuk penjumlahan pada excel. SUM digunakan untuk menjumlahkan semua angka dalam satu atau beberapa rentang sel.

Sintaks dasar fungsi SUM yaitu:

=SUM(number1, [number2], [number3], ...)

Penjelasan

• number1: Rentang sel pertama yang ingin dijumlahkan (misalnya, C2:C100)
• [number2], [number3], ...: (Opsional) Rentang sel atau angka tambahan

Dari sintaks tersebut, dapat kita ketahui bahwa penjumlahan menggunakan fungsi SUM tidak hanya terbatas pada satu rentang nilai saja, namun beberapa rentang sekaligus.

Misal ingin dihitung berapa total uang yang perlu dikeluarkan untuk pembayaran gaji semua karyawan. Maka kita dapat menjumlahkan langsung seluruh data pada kolom Gaji (E3:E17) dan menggunakan fungsi SUM.

FORMULA

=SUM(E3:E17)

Fungsi SUM juga dapat digunakan untuk menjumlahkan beberapa rentang nilai sekaligus. Misal kita ingin menjumlahkan gaji karyawan dari divisi Pemasaran. Informasi untuk karyawan dari divisi pemasaran tersebar pada beberapa rentang, sehingga kita harus menggunakan fungsi sum dan memasukkan semua rentang yang sesuai.

catatan: menggunakan SUM untuk tugas seperti ini bukanlah cara yang tepat. Contoh ini hanya untuk menunjukkan fleksibilitas dari fungsi SUM.

FORMULA

=SUM(E6:E7, E12, E14)

Fungsi SUMIF memungkinkan kita menjumlahkan data yang memenuhi satu kondisi atau kriteria tertentu. Misal pada contoh sebelumnya, ketika kita ingin menjumlahkan Gaji karyawan khusus divisi Pemasaran, tentunya akan menjadi sulit jika data sangat banyak dan tersebar di beberapa baris yang terpisah.

Dengan fungsi SUMIF hal tersebut menjadi mudah dilakukan, kita tidak perlu memilih rentang data secara manual, namun cukup menentukan kriteria tertentu sebagai dasar penjumlahan. Misalkan jumlah gaji karyawan dari Divisi Pemasaran atau divisi lainnya, jumlah gaji karyawan dengan jabatan supervisor atau mungkin total gaji karyawan dengan nilai lebih besar dari 7.500.000 rupiah.

Sintaks dasar SUMIF yaitu:

=SUMIF(range, criteria, [sum_range])

Penjelasan:

• range: Rentang sel yang akan dievaluasi berdasarkan kriteria (misalnya, kolom divisi C3:C17)
• criteria: Kondisi yang harus dipenuhi. Bisa berupa teks, angka, wildcard, atau ekspresi logika (misalnya, "Pemasaran", "Pro*", ">7500000")
• [sum_range]: (Opsional) Rentang sel yang berisi angka untuk dijumlahkan. Jika dikosongkan, Excel akan menjumlahkan range itu sendiri

Fungsi SUMIF memungkinkan kita untuk menghitung total gaji karyawan menurut divisi. Misal untuk divisi Pemasaran dapat dihitung dengan formula:

=SUMIF(C3:C17, "Pemasaran", E3:E17)

Formula di atas akan mencari baris-baris di C3:C17 yang memiliki nilai "Pemasaran" dan kemudian akan menjumlahkan setiap nilai pada baris-baris E3:E17 yang bersesuaian.

Dengan cara yang sama kita dapat menghitung total gaji untuk setiap divisi. Kita dapat menuliskan nama setiap divisi pada sel-sel (misalnya G3, G4 dan G5), kemudian menggunakan SUMIF untuk menghitung jumlah gaji setiap sel tersebut. Agar formula mudah di copy-paste kita dapat mengunci kolom range yaitu $C$3:$C$17 serta kolom untuk sum_range yaitu $E3:$E$17.

FORMULA

=SUMIF($C$3:$C$17, G3, $E$3:$E$17)      // Sel H3 -> untuk Pemasaran
=SUMIF($C$3:$C$17, G4, $E$3:$E$17)      // Sel H4 -> untuk Produksi
=SUMIF($C$3:$C$17, G5, $E$3:$E$17)      // Sel H5 -> untuk Keuangan

Kriteria pencarian tidak hanya mencari teks atau angka yang sesuai. Sebaliknya, kita dapat menggunakan "<>teks" untuk mengecualikan kata tertentu dalam pencarian.

=SUMIF(C3:C17, "<>Keuangan", E3:E17)

Dari Contoh 1 dan Contoh 2, kita juga dapat menggunakan kriteria apakah teks kosong atau sebaliknya.

Formula berikut akan menghitung jumlah karyawan untuk kolom Divisi yang tidak kosong. Pada contoh data di atas, kebetulan tidak ada baris kosong pada kolom Divisi, sehingga akan menjumlahkan semua baris.

=SUMIF(C3:C17, "<>", E3:E17)

Sebaliknya kita dapat juga menghitung gaji karyawan untuk kolom Divisi yang kosong. Tentunya untuk contoh ini hasil penjumlahan SUMIF adalah 0, karena tidak ada baris pada kolom Divisi yang kosong.

=SUMIF(C3:C17, "", E3:E17)

Fungsi SUMIF mendukung pencarian berdasarkan wildcard menggunakan simbol "*". Penggunaan simbol ini memungkinkan penggunaan kriteria yang lebih fleksibel dibandingkan menggunakan kata secara lengkap.

Berikut beberapa contohnya:

Jumlah Gaji Karyawan dengan Jabatan Diawali “Sup”

Untuk mendapatkan jumlah gaji karyawan dengan jabatan seperti Supervisor atau lainnya yang diawali kata Sup maka kita dapat menggunakan kriteria "Sup*" seperti berikut:

=SUMIF(D3:D17, "Sup*", E3:E17)

Jumlah Gaji Karyawan untuk Divisi Berakhiran “an”

Sebaliknya, kita juga dapat mengunakan kriteria kata yang diakhiri pola tertentu. Contoh, untuk menghitung jumlah gaji karyawan dari divisi yang diakhiri karakter "an" (Keuangan dan Pemasaran) dapat menggunakan kriteria "*an".

=SUMIF(C3:C17, "*an", E3:E17)

Jumlah Gaji Karyawan untuk Jabatan yang Mengandung Karakter “*p*o*”

Kriteria pencarian juga dapat menggunakan pola karakter pada tengah kata. Misalnya untuk mencari jumlah gaji karyawan dengan jabatan Supervisor dan Operator di mana keduanya mengandung pola "*p*o*".

=SUMIF(D3:D17, "*p*o*", E3:E17)

catatan: contoh ini juga dapat menggunakan pola sebelumnya (menggunakan "*or"), di mana Supervisor dan Operator sama-sama diakhiri dengan karakter “or”

Selain menggunakan simbol "*", kriteria pencarian juga dapat ditentukan dengan simbol "?". Perbedaannya adalah simbol "?" menunjukkan persis 1 karakter. Misalkan pencarian berdasarkan kriteria nama karyawan sebagai berikut:

Mencari setiap nama karyawan yang terdiri dari 4 karakter ("????"):

=SUMIF(B3:B17, "????", E3:E17)

Mencari Nama Karyawan yang Terdiri dari 4 Karakter dan Berakhiran “i” ("???i"):

Karyawan dengan nama Andi, Budi, Dedi, dan Luki akan cocok, namun Evi dan Ovi tidak, karena hanya terdiri dari 3 karakter.

=SUMIF(B3:B17, "???i", E3:E17) 

Mencari Nama Karyawan yang Terdiri dari 5 Karakter dan Karakter Kedua “a” "?a???":

Hanya karyawan dengan nama Fajar dan Karin yang cocok dengan kriteria ini.

=SUMIF(B3:B17, "?a???", E3:E17) 

Parameter sum_range pada fungsi SUMIF sifatnya adalah opsional. Jika parameter range berupa numerik dan sum_range tidak ditentukan, maka fungsi SUMIF secara otomatis akan mengembalikan penjumlahan nilai dari kolom range yang sesuai kriteria.

Menghitung Total Gaji Karyawan dengan Gaji 6.000.000

Karena sum_range tidak ditentukan, maka otomatis SUMIF akan menjumlahkan nilai dari rentang E3:E17 dengan nilai 6000000. Pada data yang kita miliki, terdapat 3 orang dengan gaji sebesar 6.000.000 sehingga fungsi berikut akan mengembalikan nilai 18.000.000.

=SUMIF(E3:E17, 6000000) 

Menghitung Total Gaji Karyawan dengan Gaji Lebih dari 10.000.000

Seperti saat bekerja dengan teks, kita juga dapat menggunakan simbol-simbol seperti "<>", ">", "<", ">=" dan "<=" sebagai kriteria pencarian fungsi SUMIF.

Misalkan untuk menghitung Total gaji dari karyawan dengan gaji lebih dari 10.000.000, maka kita dapat menggunakan kriteria ">10000000". Terdapat 3 karyawan dengan gaji di atas 10.000.000 dengan total sebesar 33.500.000.

=SUMIF(E3:E17, ">10000000") 

Fungsi SUMIFS memungkinkan kita menjumlahkan data berdasarkan beberapa kriteria sekaligus. Penentuan kriteria pada fungsi SUMIFS juga sama seperti fungsi SUMIF sebelumnya.

Sintaks dasar SUMIFS yaitu:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Penjelasan:

• sum_range: Rentang sel yang berisi angka yang akan dijumlahkan
• criteria_range1: Rentang pertama yang akan diperiksa
• criteria1: Kriteria untuk criteria_range1
• criteria_range2, criteria2, ...: Kriteria tambahan (opsional)

catatan: penting diperhatikan, pada SUMIFS parameter sum_range merupakan parameter pertama fungsi. Hal ini berkebalikan dengan fungsi SUMIF di mana parameter sum_range merupakan parameter terakhir.

FORMULA

=SUMIFS(E3:E17, C3:C17, "<>Keuangan", D3:D17, "Staf")

• Mahir Fungsi Excel: GROUPBY
• Mahir Fungsi Excel: BYROW dan BYCOL
• Mahir Fungsi Excel: MAP
• Membuat Fungsi Excel Sendiri Tanpa VBA