Fungsi SUMIFS() di Excel: Menjumlahkan Data dengan Beberapa Kriteria

Fungsi SUMIFS() di Excel menjumlahkan angka berdasarkan beberapa kondisi untuk memberi kita kendali yang presisi atas perhitungan. Berbeda dengan SUMIF() yang memeriksa satu kondisi, SUMIFS() menangani beberapa kondisi sekaligus.

Dalam artikel ini, saya akan menunjukkan cara menggunakan SUMIFS() dengan contoh lanjutan dan tips pemecahan masalah. Pada akhirnya, Anda akan tahu cara menggunakan SUMIFS() untuk membuat perhitungan Anda lebih akurat dan efisien.

Jawaban Singkat

Untuk menggunakan fungsi SUMIFS() di Excel:

• Ketik =SUMIFS( untuk memulai fungsi.

• Pilih rentang yang berisi nilai yang akan dijumlahkan.

• Pilih rentang tempat kondisi akan diterapkan.

• Masukkan kondisi yang ingin dicocokkan.

• Tutup kurung dan tekan Enter.

Di sini, saya telah menjumlahkan semua penjualan yang lebih besar dari 200.

=SUMIFS(B2:B8, B2:B8, ">200")

Jumlahkan nilai menggunakan SUMIFS(). Gambar oleh Penulis.

Memahami Fungsi SUMIFS()

Fungsi SUMIFS() di Excel hanya menjumlahkan nilai ketika semua kondisi yang ditentukan terpenuhi. Fungsi ini mendukung operator logika seperti lebih besar dari >, lebih kecil dari <, sama dengan =, dan tidak sama dengan <>, serta karakter wildcard untuk pencocokan sebagian. Fungsi ini bekerja dengan angka, teks, dan tanggal.

Sintaks SUMIFS()

Fungsi SUMIFS() terlebih dahulu mendefinisikan rentang nilai yang akan dijumlahkan, diikuti pasangan rentang kondisi dan kriterianya. Perhatikan bahwa SUMIFS() dapat menangani lebih dari satu kondisi logis.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Berikut penjelasannya:

• sum_range adalah rentang sel yang ingin Anda jumlahkan.

• criteria_range1 adalah rentang pertama tempat kita memeriksa suatu kondisi.

• criteria1 adalah kondisi yang diterapkan pada criteria_range1.

• [criteria_range2, criteria2], … bersifat opsional. Anda dapat menambahkan lebih banyak pasangan rentang dan kondisi. Setiap pasangan menambah kondisi lain yang harus terpenuhi.

Perhatikan bahwa criteria_range (rentang tempat kondisi diperiksa) harus memiliki jumlah baris dan kolom yang sama dengan sum_range (rentang yang dijumlahkan).

Contoh SUMIFS()

Mari coba contoh dasar. Saya memiliki dataset buah dengan kuantitas dan penjualannya. Dan saya ingin mencari total penjualan Apel di mana Kuantitas lebih dari 5. Jadi rumus saya menjadi:

=SUMIFS(C2:C8, A2:A8, "Apples", B2:B8, ">5")

Di sini C2:C8 adalah rentang nilai yang dijumlahkan (Sales ($)). A2:A8, "Apples" adalah kondisi untuk hanya menyertakan baris saat produknya Apel. Dan B2:B8, ">5" adalah kondisi untuk hanya menyertakan baris saat Kuantitas lebih dari 5.

Rumus SUMIFS() untuk menjumlahkan penjualan berdasarkan suatu kondisi. Gambar oleh Penulis.

SUMIF() vs. SUMIFS()

Perbedaan utama antara SUMIF() dan SUMIFS() adalah jumlah kondisi yang digunakan. SUMIF() menangani satu kondisi. Sedangkan SUMIFS() dapat menangani beberapa kondisi. 

Kasus Penggunaan SUMIFS() di Excel

Sekarang setelah Anda tahu cara kerja fungsi SUMIFS(), mari lihat beberapa kasus lanjutan di dunia nyata.

Menggunakan SUMIFS() dengan berbagai tipe data

SUMIFS() dapat bekerja dengan berbagai tipe data seperti angka, teks, dan tanggal. Misalnya saya memiliki laporan penjualan, tetapi beberapa baris tidak memiliki nama pelanggan. Dan saya ingin menjumlahkan hanya yang memiliki nama pelanggan. Untuk itu, saya menggunakan rumus berikut:

=SUMIFS(C2:C5, A2:A5, "<>")

Rumus ini melihat kolom Customer, memeriksa sel yang tidak kosong, lalu menjumlahkan penjualan yang sesuai.

Jumlahkan semua sel yang tidak kosong dengan fungsi SUMIFS(). Gambar oleh Penulis.

Untuk menjumlahkan penjualan saat nama pelanggan tidak ada, saya menggunakan rumus ini:  

=SUMIFS(C2:C10, A2:A10, "", B2:B10, "")

Jumlahkan sel kosong dengan fungsi SUMIFS(). Gambar oleh Penulis.

Alih-alih menuliskan kondisi secara langsung, kita bisa menyimpannya di sel lain. Jika saya ingin menjumlahkan penjualan untuk Sarah Lee, saya mengetiknya di sel E3 dan menggunakan rumus ini:

=SUMIFS(C2:C5, A2:A5, E3)

Setiap kali nilai di E3 berubah, total akan diperbarui secara otomatis. 

Gunakan SUMIFS() untuk menjumlahkan nilai menggunakan referensi sel. Gambar oleh Penulis.

Menerapkan logika OR di SUMIFS()

Kita tahu bahwa SUMIFS() hanya menggunakan logika AND, yang berarti semua kondisi yang kita tetapkan harus benar agar nilai dijumlahkan. Namun, jika kita ingin menggunakan logika OR, yang menjumlahkan nilai meskipun salah satu dari beberapa kondisi terpenuhi, kita dapat melakukannya dengan menggabungkan beberapa SUMIFS().

Misalnya, jika saya ingin menjumlahkan penjualan untuk John Doe ATAU Drake White, saya menggabungkan dua rumus SUMIFS() seperti ini:

=SUMIFS(C2:C8, A2:A8, "John Doe") + SUMIFS(C2:C8, A2:A8, "Drake White")

Karena Drake White tidak ada dalam daftar, bagian rumus itu mengembalikan 0. Total hanya menyertakan penjualan untuk John Doe, yang menunjukkan bagaimana logika OR bekerja.

Gunakan logika OR dalam fungsi SUMIFS(). Gambar oleh Penulis.

Di Excel 365 atau yang lebih baru, kita dapat menyederhanakannya dengan rumus array dan hasilnya akan sama.

=SUMIFS(C2:C8,A2:A8,{"John Doe","Drake White"})

Gunakan rumus array SUMIFS(). Gambar oleh Penulis.

SUMIFS() dengan named range

Kita juga dapat menggunakan named range alih-alih referensi sel seperti A2:A5 atau C2:C5. Pada contoh ini, saya memilih A2:A5 dan menamainya Customer_list serta memilih C2:C5 dan menamainya Sales_list. Sekarang untuk menemukan total penjualan John, saya menggunakan rumus ini: 

=SUMIFS(Sales_list, Customer_list, "John")

Jumlahkan nilai dengan SUMIFS() menggunakan named range. Gambar oleh Penulis.

Menggunakan wildcard untuk pencocokan sebagian

Wildcard adalah karakter khusus (* atau ?) yang mewakili karakter tidak diketahui atau variabel dalam pencarian teks dan pencocokan pola: 

• * mencocokkan sejumlah karakter apa pun (termasuk nol).

• ? mencocokkan tepat satu karakter.

Begini cara kerjanya: 

• A* akan mencocokkan semua sel yang dimulai dengan A.

• *A akan mencocokkan semua sel yang diakhiri dengan A.

• *A* akan mencocokkan semua sel yang berisi A di mana saja.

Fungsi SUMIFS() menawarkan kemampuan pencocokan teks melalui karakter wildcard. Fungsi ini mengidentifikasi nilai yang dimulai dengan, diakhiri dengan, atau mengandung pola teks tertentu. Misalnya saya ingin menjumlahkan penjualan untuk Kode Produk yang dimulai dengan ABC. Dalam hal ini, rumus saya seperti ini:

=SUMIFS(D2:D8, C2:C8, "ABC*")

Untuk menjumlahkan penjualan yang diakhiri dengan 00, saya menulis:

=SUMIFS(B2:B7, A2:A7, "*00")

Gunakan wildcard di SUMIFS(). Gambar oleh Penulis.

Menjumlahkan berdasarkan rentang tanggal

SUMIFS() juga dapat menjumlahkan nilai dalam rentang tanggal tertentu. Jika saya ingin mengetahui semua penjualan pada Januari 2023, saya menggunakan rumus berikut: 

=SUMIFS(D2:D8, B2:B8, ">=01-Jan-2023", B2:B8, "<=31-Jan-2023") 

Rumus ini akan memeriksa tanggal dari 1 Januari 2023 hingga 31 Januari 2023 lalu menjumlahkan semua penjualan. 

Jumlahkan rentang tanggal menggunakan SUMIFS(). Gambar oleh Penulis.

Menjumlahkan nilai dengan beberapa kondisi numerik

Fungsi SUMIFS() dapat menjumlahkan nilai yang berada dalam rentang numerik tertentu. Di sini, saya memiliki dataset acak dan ingin menjumlahkan nilai di mana Sales lebih dari 100 tetapi kurang dari 300. Untuk ini, saya menggunakan rumus berikut:  

=SUMIFS(C2:C5, C2:C5, ">100", C2:C5, "<300")

Rumus ini terlebih dahulu menentukan rentang sel yang akan dijumlahkan (C2:C5) sebagai argumen pertama. Lalu, menerapkan dua kondisi yang disebutkan di atas pada rentang yang sama. Fungsi SUMIFS() secara bersamaan mengevaluasi setiap sel dalam rentang terhadap kedua kriteria. Dan Anda dapat melihat hanya sel dengan nilai antara 100 dan 300 (eksklusif) yang disertakan dalam jumlah. 

Jumlahkan nilai dalam rentang angka menggunakan SUMIFS(). Gambar oleh Penulis.

Pemecahan Masalah Jika SUMIFS() Tidak Berfungsi

Jika fungsi SUMIFS() Anda tidak berfungsi sebagaimana mestinya, periksa masalah berikut:

Ukuran rentang tidak cocok 

Jika rumus SUMIFS() Anda menghasilkan error, kemungkinan karena ukuran rentang yang tidak cocok. Dalam SUMIFS(), semua rentang yang Anda pilih, seperti sum_range dan criteria_ranges, harus memiliki jumlah baris dan kolom yang sama.

Contohnya, pada rumus ini rentang jumlah adalah B2:B8, tetapi rentang kriteria adalah A2:A7, yang memiliki satu baris lebih sedikit:

=SUMIFS(B2:B8, A2:A7, "Drake White")

Ketidakcocokan ini akan menghasilkan error #VALUE!. Jadi, pastikan semua rentang berukuran sama, seperti ini:

=SUMIFS(B2:B8, A2:A8, "John Doe")

Format kriteria teks yang tidak benar 

Dalam SUMIFS(), Anda harus meletakkan kriteria teks dalam tanda kutip. Jika tidak, hasilnya bisa tidak tepat. Misalnya, rumus ini akan mengembalikan 0 karena Excel melihat Drake White sebagai nama atau variabel, bukan sebagai teks: 

=SUMIFS(B2:B8, A2:A8, Drake White)

Untuk memperbaikinya, selubungi Drake White dengan tanda kutip seperti ini dan hasilnya akan benar:

=SUMIFS(B2:B8, A2:A8, "Drake White")

Nilai nol yang tidak terduga

Jika rumus mengembalikan 0 saat Anda mengharapkan hasil, bisa jadi karena pemformatan kondisi numerik atau berbasis tanggal yang tidak benar. Dalam SUMIFS(), kondisi seperti >100 atau <01-Jan-2023 harus ditulis sebagai string teks dalam tanda kutip. Jika tidak, akan mengembalikan error atau 0 karena kondisinya tidak dalam tanda kutip.

Atau kita bisa menyimpan tanggal di sel terpisah (misalnya, C1) dan gunakan ini:

=SUMIFS(B2:B8, A2:A8, ">="&C1)

SUMIFS() tidak berfungsi dengan transformasi array

SUMIFS() tidak mendukung transformasi tertentu seperti mengekstrak komponen spesifik dari data di dalam rumus. Misalnya, jika saya ingin menjumlahkan penjualan bulan Januari dan Maret, SUMIFS() tidak akan bekerja langsung dengan kriteria array {"January","March"}. 

Untuk menunjukkan ini, misalkan saya memiliki bulan di kolom A (January, February, March, April) dan nilai penjualan di kolom B (100, 200, 300, 400). Rumus

=SUMIFS(B2:B5, A2:A5, {"January","March"})

tidak akan mengembalikan jumlah yang diharapkan yaitu 400 (100 + 300). Sebagai gantinya, kita bisa menggunakan SUMPRODUCT():

=SUMPRODUCT((A2:A5={"January","March"})*B2:B5)

atau membungkus SUMIFS() dengan SUM() menggunakan rumus ini:

=SUM(SUMIFS(B2:B5, A2:A5, {"January","March"}))

Kedua solusi akan menangani kriteria array dengan benar dan mengembalikan jumlah penjualan untuk Januari dan Maret.

Praktik Terbaik Menggunakan SUMIFS()

Untuk memaksimalkan fungsi SUMIFS(), kita harus menghindari kesalahan umum dengan mengikuti praktik terbaik berikut: 

• Gunakan wildcard untuk pemfilteran yang fleksibel: Sebaiknya gunakan wildcard saat memfilter data karena terkadang data kita tidak tertata dengan sempurna. Mungkin nama produk tidak selalu dieja sama, atau ada spasi tambahan. Wildcard membantu Anda mencocokkan pola tanpa membutuhkan teks yang persis sama.

• Hindari hardcode kriteria: Alih-alih mengetik kondisi langsung ke rumus, simpan di sel terpisah. Jika Anda hardcode kondisi seperti "Drake White" atau ">100", Anda harus mengedit rumus setiap kali ingin mengubahnya. Namun, dengan referensi sel, rumus akan diperbarui secara otomatis.

• Gunakan referensi absolut saat menyalin rumus: Saat Anda menyalin rumus SUMIFS() ke sel lain, Excel secara otomatis menyesuaikan referensi sel. Jika Anda tidak ingin ini terjadi, gunakan referensi absolut dengan menambahkan $ untuk mengunci sel.

Penutup

SUMIFS() adalah fungsi Excel yang membantu kita menganalisis data dengan memfilter dan menjumlahkan angka yang sesuai dengan kriteria tertentu. Fungsi ini sangat berguna untuk tugas seperti melacak penjualan per wilayah, merangkum biaya per kategori, atau menghitung inventaris berdasarkan jenis produk. Setelah Anda terbiasa dengan SUMIFS(), Anda akan menyadari fungsi ini menghemat waktu dan membantu menemukan tren penting dalam data tanpa solusi yang rumit.

Jika Anda ingin mengembangkan keterampilan ini, lihat kursus Data Analysis in Excel, kursus Data Preparation in Excel, dan kursus Data Visualization in Excel kami atau jalur keterampilan Excel Fundamentals. Kursus-kursus tersebut akan membantu Anda mempertajam keterampilan yang sudah ada.