Pada kesempatan sebelumnya, kami menunjukkan bagaimana menggunakan fungsi SUMIF.
Pada salah satu bagian di artikel tersebut, kami ingin menjumlahkan dengan kategori range tanggal, tapi tidak bisa karena SUMIF hanya memperbolehkan satu kriteria untuk satu fungsi.
Solusi bagi masalah tersebut adalah menggunakan SUMIFS pada Excel.
Dapatkan rumus-rumus yang digunakan screenshot artikel ini pada file di bawah.
File: fungsi-sumifs-excel.xlsx.
Apa Itu Fungsi SUMIFS pada Excel?
Fungsi SUMIFS pada Excel adalah fungsi penjumlahan yang mampu menerima lebih dari satu kriteria di dalam satu fungsi.
Sintaks
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- sum_range (wajib) adalah range dari nilai-nilai yang ingin dijumlahkan.
- criteria_range1 (wajib) adalah range dari nilai-nilai yang ingin dievaluasi di dalam kriteria.
- criteria1 (wajib) adalah kriteria yang ingin kita terapkan terhadap criteria_range1 untuk mengambil nilai-nilai yang sesuai di sum_range.
- criteria_range2 sampai criteria_range127 (opsional) adalah range kedua sampai 127 yang ingin diperbandingkan.
- criteria2 sampai criteria127 (opsional) adalah kriteria kedua sampai 127 bagi criteria_range2 sampai criteria_range127.
Perbandingan default kriteria di SUMIFS adalah sama dengan dan setiap kondisi digabungkan dengan ekspresi logika And.
Jadi bila kita punya dua kriteria, maka yang kita sampaikan ke SUMIFS adalah jumlahkan nilai-nilai pada sum_range bila kriteria1 DAN kriteria2 memenuhi kondisi.
Perbedaan SUMIF dan SUMIFS
Perbedaan SUMIF dan SUMIFS terletak pada jumlah kriteria yang bisa diterima oleh kedua fungsi tersebut.
Fungsi SUMIF hanya mampu menerima satu kriteria.
Fungsi SUMIFS Excel mampu memasukkan hingga 127 kriteria dengan kriteria pertama adalah wajib dan selanjutnya adalah opsional.
Saat SUMIFS menerima satu kriteria, nilai yang dikembalikan sama dengan SUMIF.
Cara Menggunakan Fungsi SUMIFS pada Excel
Sebelum menggunakan fungsi SUMIFS sebagai rumus, ketahui bahwa bentuk kriteria yang digunakan oleh SUMIFS sama dengan SUMIF.
Di sini kami tidak membahas sepenuhnya mengenai bagaimana membentuk kriteria SUMIFS. Teman-teman monggo baca dulu mengenai SUMIF.
Rumus SUMIFS Excel: Bentuk Dasar
Dalam bentuk dasarnya, rumus SUMIFS Excel hanya mengolah parameter-parameter wajib.
Pada rumus di bawah, kami ingin mengetahui berapa besaran komisi yang diterima oleh Wayan.
=SUMIFS(D2:D11; B2:B11; "Wayan")
Karena kriteria pembanding default SUMIFS adalah sama dengan, maka kita cukup menuliskan "Wayan".
Bila teman-teman merasa lebih nyaman menuliskan pembanding, rumusnya seperti di bawah:
=SUMIFS(D2:D11; B2:B11; "=Wayan")
Karena bentuk dasar SUMIFS pada dasarnya sama dengan SUMIF, maka rumus berikut akan memberikan hasil yang sama, yaitu 78,890,000.
=SUMIF(B2:B11; "Wayan"; D2:D11)
SUMIFS Tidak Sama Dengan
Kriteria tidak sama dengan diperoleh dengan menggunakan operator pembanding <>.
Rumus berikut akan menjumlahkan komisi untuk penjual bukan Wayan dan rumah. Dengan demikian membentuk rumus SUMIFS dengan dua kriteria.
=SUMIFS(D2:D11; B2:B11; "<>"&B13; C2:C11; "<>"&B14)
Perhatikan. Saat menggunakan referensi cell, pisahkan referensi tersebut dari teks pembanding dengan tanda ampersand (&).
Rumus SUMIFS dengan Range Tanggal
Kriteria dalam bentuk range atau rentang, pada dasarnya adalah kriteria dengan minimal dua kondisi.
Salah satu bentuk kriteria range adalah kondisi di antara dua tanggal.
Pada contoh rumus SUMIFS berikut, kita ingin menjumlahkan komisi bagi penjualan pada tanggal 1 Januari sampai 5 Januari 2024.
Secara logika, rumus di antara dua tanggal (atau dua nilai lainnya) adalah lebih besar (atau sama dengan) dari nilai pertama DAN lebih kecil (atau sama dengan) dari nilai kedua.
Karena itu, rumus yang kita butuhkan adalah:
=SUMIFS(D2:D11; A2:A11; ">="&A2; A2:A11; "<="&A6)
Bila kita ingin membuat rumus SUMIFS dengan tiga kriteria, empat, atau lebih, cukup tambahkan di sampingnya.
=SUMIFS(D2:D11; A2:A11; ">="&A2; A2:A11; "<="&A6; B2:B11; "Eko")
=SUMIFS(D2:D11; A2:A11; ">="&A2; A2:A11; "<="&A6; B2:B11; "Eko"; C2:C11; "Rumah")
Tips: Excel memang mengijinkan kita menggunakan sampai 127 kriteria. Saran kami adalah gunakan kriteria secukupnya.
Makin banyak kriteria, rumus akan makin sulit dibaca. Makin sulit juga dirawat.
SUMIFS Beda Sheet
Tidak ada perbedaan antara menggunakan rumus beda sheet di SUMIFS dengan rumus-rumus beda sheet Excel lainnya. Sama aja.
Kita dapat menempatkan referensi antar sheet di parameter mana pun di SUMIFS.
=SUMIFS('Gambar 04.Data'!D2:D11; 'Gambar 04.Data'!B2:B11; 'Gambar 04'!A2; 'Gambar 04.Data'!C2:C11; 'Gambar 04'!B2)
Teman-teman cuma perlu waspada bagaimana menulis referensi antar sheet.
Cara yang sama juga berlaku untuk rumus SUMIFS beda file atau workbook.
Rumus SUMIFS dengan Or
Sebelumnya kami sampaikan bahwa hubungan antara setiap kriteria di SUMIFS memakai ekspresi logika And.
Bagaimana bila kita ingin menggunakan ekspresi Or? Sebagai contoh, kita ingin melihat penjualan oleh Eko atau Indra saja.
Caranya adalah dengan menggunakan array.
=SUM(SUMIFS(E2:E11; B2:B11; {"Eko"; "Indra"}))
Pada rumus di atas, kami ingin mendapatkan total komisi dari Eko atau Indra. Dengan menggunakan array, kami akan mendapatkan hasil berupa array juga. Masing-masing array berisi perolehan komisi Eko dan Indra.
Catatan: Kita juga membuat dua rumus SUMIFS, masing-masing untuk Eko dan Indra, lalu menjumlahkannya dengan SUM. Tapi, cara ini lebih panjang.
Setiap hasil kemudian dijumlahkan dengan fungsi SUM.
Mungkin teman-teman heran. Kok gak pake rumus berikut aja?
= SUMIFS(E2:E11; B2:B11; "Eko"; B2:B11; "Indra")
Bila dijalankan, rumus di atas akan mengembalikan nol.
Kok, nol?
Karena rumus tersebut berarti menjumlahkan komisi untuk setiap penjualan oleh Eko dan Indra.
Kriteria pertama menyaring penjualan oleh Eko. Hasilnya kemudian kita saring lagi dengan kriteria penjualan Indra. Sudah pasti gak ada, kan?
Seperti terlihat pada tabel, setiap penjualan hanya dilakukan oleh satu penjual.
Di sini teman-teman harus hati-hati dalam menggunakan dua kriteria untuk satu criteria_range.
Kenapa Rumus SUMIFS Tidak Berfungsi?
Pada artikel SUMIF, kami membahas beberapa penyebab SUMIF tidak bekerja.
Berhubung SUMIFS mirip dengan SUMIF, teman-teman bisa mengunjungi artikel tersebut.