Rumus SUMIFS Excel adalah fungsi untuk menjumlahkan data dalam beberapa cell yang memenuhi kriteria tertentu. Dalam menjumlahkan data, SUMIFS bisa menggunakan 1 / 2 / 3 / 4 kriteria atau lebih.
Penting! Menjumlahkan tidak sama dengan menghitung. Jadi kegunaan rumus SUMIFS sangat berbeda dengan COUNTIFS. SUMIFS akan menjumlahkan angka sedangkan COUNTIFS akan menghitung banyak data. Jika Anda ingin menghitung banyak data, silahkan pelajari SUB-BAB COUNTIFS (lihat Pintasan Panduan).
Pada dasarnya, rumus SUMIFS dapat digunakan untuk menjumlahkan nilai dalam beberapa cell yang memenuhi kriteria berdasar tanggal, angka dan teks.
Selain itu, SUMIFS juga mendukung operator perbandingan logika (seperti =, <, >, <=) serta karakter wildcard (seperti * dan ?) sebagai kriteria penjumlahan data.
Namun, sebelum belajar menggunakan rumus SUMIFS, pahami dahulu perbedaannya dengan fungsi SUM dan SUMIF berikut:
Table of Contents
Perbedaan Rumus SUM, SUMIF dan SUMIFS Excel
Pada dasarnya, rumus SUMIFS Excel memiliki kemampuan fungsi SUM untuk menjumlahkan data (nilai) serta fungsi IF untuk menentukan kriteria (pengujian logika) dalam menjumlahkan data. Jika digabungkan akan menjadi fungsi SUMIF.
Namun, fungsi SUMIF hanya bisa menggunakan 1 kriteria dalam menjumlahkan nilai. Misalnya seperti contoh rumus SUMIF tanpa argument [sum_range] berikut:
Rumus SUMIF pertama =SUMIF(B3: B14,50) akan menjumlahkan semua angka 50 dari cell B3 sampai B14. Jadi kriteria penjumlahan dalam rumus ini hanya ada satu, yaitu 50.
Begitu juga dengan SUMIF yang ke 2, 3, dan 4 masing-masing menggunakan kriteria >50, D10 dan >D10.
Sementara Fungsi SUMIFS dapat menggunakan lebih dari satu kriteria hingga maksimal 127 kriteria dalam satu rumus. Jadi tak heran bila banyak pengguna yang menyebut fungsi SUMIFS sebagai rumus SUMIF bertingkat.
Apa benar ? Coba pahami aturan penulisannya…
Aturan Penulisan Rumus SUMIFS
Penting! Sebelum melanjutkan, ketahui dahulu bahwa operator pemisah rumus Excel untuk regional Indonesia berbeda dengan regional English. Indonesia menggunakan titik koma (;), sedangkan English menggunakan koma (,). Software Excel Saya menggunakan regional English. Jika Anda ragu, silahkan pelajari panduan Format English vs Indonesia Excel.
Sama seperti rumus Excel lainnya, SUMIFS juga memiliki aturan penulisan untuk memenuhi setiap argument penting seperti berikut:
- sum_range (wajib) merupakan range data yang akan dijumlahkan nilainya.
- criteria_range1 (wajib) merupakan range data yang berisi kriteria pertama. Jumlah baris dan kolom criteria_range harus sama banyak dengan argumen sum_range.
- criteria1 (wajib) merupakan kriteria pertama yang ingin diterapkan.
- criteria_range2 (optional) merupakan range data yang berisi kriteria kedua.
- criteria2 (optional) merupakan kriteria kedua yang ingin diterapkan.
- … (optional) merupakan singkatan dari dan argumen seterusnya. Artinya Anda bisa menambahkan [criteria_range3,criteria3], [criteria_range4,criteria4] hingga 127 kriteria.
Note: Kriteria dalam rumus SUMIFS memiliki 2 argument berpasangan. Misalnya argument criteria_range1 dan criteria1. Argument criteria1 akan menyesuaikan kriteria berdasarkan data dalam criteria_range1. Begitu juga jika Anda ingin menambahkan kriteria ke dua, tiga atau lebih.
Sebagai pemahaman dasar, ada baiknya pahami dahulu cara menggunakan rumus SUMIFS Excel menggunakan 1 kriteria berikut:
Rumus SUMIFS Excel Menggunakan 1 Kriteria
Saya mulai dari contoh yang paling sederhana. Misalnya, Saya memiliki data penjualan produk per pelanggan seperti tabel berikut:
NOPELANGGANTOTAL PENJUALAN
| 1 | TOKO A | 18 |
| 2 | TOKO B | 19 |
| 3 | TOKO B | 18 |
| 4 | TOKO A | 11 |
| 5 | TOKO B | 12 |
| 6 | TOKO A | 15 |
| 7 | TOKO A | 9 |
| 8 | TOKO B | 11 |
Tabel: Contoh cara menggunakan rumus SUMIFS Excel
Kemudian, Saya ingin menjumlahkan Total Penjualan untuk TOKO A.
Jika menggunakan rumus SUMIF, Anda bisa menggunakan fungsi berikut: =SUMIF(B2: B9,"TOKO A",C2:C9). Sementara jika menggunakan rumus SUMIFS, silahkan ikuti tahap-tahap berikut:
- Ketik rumus =SUMIFS(
- Masukkan range data yang akan dijumlahkan nilainya (sebagai argument sum_range) yaitu =SUMIFS(C2:C9
- Ketik operator pemisah rumus (tanda ; atau ,).
- Masukkan argument criteria_range1. Karena kriteria pertama merupakan nama toko pelanggan (terdapat pada kolom B), maka masukkan range data tersebut. Jadi rumus SUMIFS menjadi seperti berikut: =SUMIFS(C2:C9,B2: B9. Pastikan jumlah baris dan kolom criteria_range1 sama banyak dengan argument sum_range).
- Ketik operator pemisah rumus (tanda ; atau ,).
- Masukkan kriteria pertama yaitu “TOKO A”. Jangan lupa ketik tutup kurung dan tekan enter. Ini hasilnya:
Bagaimana, benar bukan ? Baik rumus SUM, SUMIF dan SUMIFS sama-sama memberikan hasil 53 yaitu jumlah dari 18 + 11 + 15 + 9 = 53.
Oh ya, mungkin makna fungsi SUMIFS berikut bisa memudahkan Anda memahami rumus:
Sudah paham contoh paling sederhana ini ? Nah, sekarang lanjut ke contoh yang lebih rumit.
Rumus SUMIFS Excel Menggunakan 2 / 3 / 4 atau Banyak Kriteria
Setidaknya jika Anda memahami Aturan Penulisan serta contoh SUMIFS menggunakan 1 kriteria diatas, Saya yakin tidak sulit menambahkan banyak kriteria. Bahkan Anda bisa menambahkan hingga 127 kriteria dengan mudah.
Yang terpenting, setiap kriteria harus terdiri dari 2 argument berpasangan yaitu criteria_range dan criteria.
Misalnya, Saya memiliki contoh yang tampak seperti gambar berikut:
Note: cell G13 menunjukkan contoh fungsi SUMIFS menggunakan satu kriteria yang terdapat pada kolom B (PELANGGAN).
Selanjutnya Saya ingin menggunakan 2 kriteria dalam SUMIFS yang mana kriteria pertama adalah Kolom B (PELANGGAN) dan kriteria kedua adalah Kolom C (PRODUK).
Untuk tujuan tersebut, silahkan ikuti cara menggunakan rumus SUMIFS dengan 2 kriteria berikut:
- Ketik rumus =SUMIFS(
- Pilih (blok) range data yang akan dijumlahkan. Dalam contoh ini range G2:G9. Kemudian jangan lupa ketik operator pemisah rumus (tanda ; atau ,).
- Masukkan argument criteria_range1 dan criteria1. Dalam contoh ini range kriteria pertama ada pada range B2: B9 dan criteria1 pada cell B14. Jangan lupa ketik operator pemisah rumus.
- Masukkan argument criteria_range2 dan criteria2 yaitu C2:C9 dan C14 sehingga rumus menjadi =SUMIFS(G2:G9,B2: B9,B14,C2:C9,C14
- Kemudian ketik tanda tutup kurung dan tekan enter. Berikut hasilnya:
Lalu bagaimana dengan SUMIFS Excel menggunakan 3 kriteria ?
Tambahkan saja argumen criteria_range3 dan criteria3. Misalnya kriteria ke tiga yaitu bulan Februari, maka tambahkan kriteria yang ke tiga ini dalam fungsi SUMIFS seperti berikut: =SUMIFS(G2:G9,B2: B9,B15,C2:C9,C15,E2:E9,D15)
Hasilnya tampak seperti cell G15 berikut:
Butuh contoh SUMIFS Excel dengan 4 kriteria ?
Okee… misalnya, kriteria ke empat adalah Kolom F (Jumlah Penjualan) lebih dari 10. Jadi, Anda bisa menambahkan kriteria ke empat ke dalam rumus SUMIFS seperti berikut:
Ini hasilnya:
Ingat! Setiap kriteria terdiri dari 2 argument berpasangan.
Lebih Banyak Contoh Tentang Rumus SUMIFS Excel
Dalam contoh diatas, Saya menggunakan cell reference untuk mengisi argument criteria1 sampai criteria4. Artinya, kriteria penjumlahan fungsi SUMIFS terdapat dalam sebuah cell.
Sementara itu, Anda bisa memaksimalkan penggunaan rumus SUMIFS menggunakan kriteria berupa angka dan teks (baik menggunakan ekspresi logika maupun tidak) serta menggunakan karakter wildcard.
Coba perhatikan beberapa contoh berikut:
#1 SUMIFS Menggunakan Kriteria Angka + Ekspresi Logika
Contoh SUMIFS yang kedua diatas menggunakan angka serta cell reference sebagai kriteria. Sementara jika Anda tidak ingin menggunakan cell reference, maka Anda boleh memasukkan angka (kriteria) secara langsung ke dalam argument rumus.
Selain itu, Anda juga boleh menggunakan Ekspresi Logika seperti =, <, > dll. sebagai operator perbandingan. Misalnya seperti hasil rumus SUMIFS berikut:
Pertama, rumus pada cell D12 =SUMIFS(G2:G9,D2: D9,150000,F2:F9,18) menggunakan kriteria berupa angka yang diketik secara langsung ke dalam argument criteria1 dan criteria2.
Karena tidak menyertakan ekspresi logika (operator perbandingan), maka Excel hanya akan menjumlahkan nilai yang sama persis dengan kriteria yaitu Cell G2+G4 = 2.700.000 + 2.700.000 = 5.400.000.
Kedua, rumus pada cell D13 dan D14 sama-sama menyertakan operator perbandingan lebih besar dari yaitu “>10” dan “>”&10.
Yang perlu dipahami adalah penulisan operator perbandingannya. Karena operator perbandingan bukan berupa angka valid, maka Anda perlu mengapitnya dengan kutip dua ” “.
Jadi jika kriteria yang Anda gunakan diketik manual kedalam rumus serta menggunakan ekspresi logika, kedua penulisan rumus ini sama-sama benar.
Ketiga, berbeda dengan rumus =SUMIFS(G2:G9,D2: D9,150000,F2:F9,“>D16“) dan =SUMIFS(G2:G9,D2: D9,150000,F2:F9,“>”&D16). Rumus ini menggunakan ekspresi logika (>) dan reference cell (D16).
Penting! Lihat gambar, hasil kedua rumus ini berbeda. Kenapa ? Karena cell reference tidak boleh diapit menggunakan kutip dua.
Jadi Anda hanya perlu mengapit operator perbandingan “>” dan menambahkan simbol ampersand (&). Kemudian tambahkan cell reference menjadi “>”&D16.
#2 SUMIFS Menggunakan Kriteria Teks + Ekpresi Logika
Bukan cuma angka, Anda juga bisa menggunakan ekspresi logika bersamaan dengan kriteria berupa teks, baik diketik manual maupun menggunakan cell reference.
Perhatikan contoh berikut:
Rumus pertama =SUMIFS(F2:F9,B2:B9,“TOKO A”,E2:E9,“Januari”) ini sudah jelas hanya akan menjumlahkan Total Penjualan untuk TOKO A pada bulan Januari yaitu: cell F2 + F5 = 18 + 11 = 29.
Rumus Kedua, =SUMIFS(F2:F9,B2:B9,“<>TOKO A”,E2:E9,“<>Januari”) bertujuan untuk menjumlahkan TOTAL PENJUALAN selain TOKO A dan selain bulan Januari.
Artinya, rumus ini akan menjumlahkan TOTAL PENJUALAN TOKO B pada bulan Februari yaitu cell F9 = 11.
Note: Operator Perbandingan “<>” artinya tidak sama dengan.
Oh ya, berhubung TOKO A dan Januari berupa teks, maka Anda perlu mengapitnya menggunakan kutip dua bersamaan dengan ekspresi logika.
Jadi Anda tidak bisa menggunakan "<>"&TOKO A atau "<>"&Januari kedalam rumus SUMIFS.
Rumus Ketiga ada perbedaan pada argument terakhir (criteria2) yaitu “<>D15“ dan “<>”&D15 sehingga memberikan hasil yang berbeda (60 vs 11).
Sekarang PR untuk Anda:
Penulisan mana yang benar antara “<>D15“ dan “<>”&D15 ? Jika sudah tahu jawabannya, silahkan coret-coret kolom komentar.
#3 SUMIFS Menggunakan Karakter Wildcard (* dan ?)
Seperti yang Saya jelaskan diawal panduan ini (lupa ? Baca Lagi), SUMIFS Excel juga mendukung penggunaan karakter wildcard ke dalam argument kriteria.
Kemampuan ini memudahkan Anda untuk menyelesaikan penjumlahan dengan kriteria yang lebih rumit.
Ada 2 jenis karakter wildcard yang umum digunakan yaitu:
- Simbol Asterisk (*): Dapat mewakili karakter apapun dalam jumlah berapapun.
- Simbol Question (?): Dapat mewakili 1 karakter apapun.
Karakter wildcard ini bisa digunakan untuk melakukan pencocokan kriteria yang serupa tanpa mengetikkan seluruh karakter dalam kriteria.
Misalnya, coba perhatikan contoh hasil rumus SUMIFS berikut:
Note: Saya mengganti data pada kolom B (PELANGGAN) untuk mempermudah pemahaman.
Dalam contoh ini, Saya hanya menggunakan 3 contoh. Sebetulnya masih banyak kombinasi lainnya. Saya mulai dari rumus yang pertama (cell D12).
Menggunakan Question Mark (?)
Lihat argument criteria1 yaitu “TOKO A?“. Hanya ada 1 question mark (?): ini mewakili 1 karakter apapun.
Jadi TOKO AB, TOKO AC, TOKO AM dsb. termasuk dalam kriteria. Jika merujuk dalam contoh, tentu hanya TOKO AR yang memenuhi kriteria.
Akhirnya Excel hanya akan menjumlahkan nilai TOTAL PENJUALAN untuk PELANGGAN TOKO AR pada Bulan Januari yaitu cell F3 + F4 = 19 + 18 = 37.
Menggunakan Asterisk (*)
Dengan karakter asterisk (*), TOKO A akan termasuk dalam kriteria yang pas serta TOKO A dengan karakter apapun dan berapapun setelahnya juga termasuk dalam kriteria.
Jadi TOKO A, TOKO AR, TOKO ABCDEFG pun juga akn termasuk dalam kriteria.
Dalam contoh ini, Excel hanya menjumlahkan data pada cell F2 + F3 + F4 + F5 = 18 + 19 + 18 + 11 = 66.
Lalu bagaimana dengan rumus yang ke 3 ?
Nah… ini tantangan untuk Anda. Dari semua materi yang sudah Anda pelajari diatas, berapa hasil dari rumus yang ke tiga berikut: =SUMIFS(F2:F9,B2: B9,”TOKO A*”,C2:C9,”MO*”)
Ingat! Karakter asterisk (*) bisa mewakili karakter apapun dan berapapun jumlahnya.
Jika sudah tahu jawabannya, silahkan coret-coret kolom komentar.
Penyebab dan Solusi Rumus SUMIFS Tidak Berfungsi
Namanya juga belajar, tentu bisa berhasil dan bisa gagal. Tapi… Saya hanya ingin 1 hal – Semua pengunjung M Jurnal berhasil menerapkan semua tutorial-tutorial yang Saya buat –
Jangan khawatir… Saya juga sering gagal. Dan hal-hal yang membuat rumus SUMIFS tidak berfungsi yang sudah pernah Saya alami adalah:
#1 SUMIFS = 0, Tapi Bukan Hasil yang Seharusnya
Rumus SUMIFS akan menghasilkan 0 (nol) apabila Anda menggunakan kriteria berupa teks namun tidak mengapitnya menggunakan kutip dua “…”. Misalnya lihat hasil rumus =SUMIFS(F2:F9,B2: B9,TOKO A) berikut:
Jadi, untuk menghindari rumus SUMIFS tidak berfungsi dan menghasilkan 0 (nol), maka apit teks menggunakan kutip dua menjadi =SUMIFS(F2:F9,B2: B9,“TOKO A”).
Karena selain angka dan cell reference, Anda harus mengapit kriteria dengan kutip dua. Coba pelajari kembali bagian lebih banyak contoh diatas.
#2 Keliru Dalam Urutan Argument Antara SUMIF dan SUMIFS
Baik SUMIF maupun SUMIFS sama-sama bertujuan menjumlahkan nilai berdasarkan kriteria. Kedua fungsi ini pun juga memiliki argument yang serupa.
Namun susunan argument kedua rumus ini berbeda. Coba kita ingat kembali apa argument kedua fungsi ini:
Lihat, argument sum_range untuk SUMIF berada di akhir. Sedangkan SUMIFS berada di awal rumus. Jadi jangan sampai keliru dengan urutan susunan argumentnya ya…
#3 Hasil #VALUE!
Rumus SUMIFS juga tidak akan berfungsi dan memberikan hasil berupa pesan error #VALUE!. Penyebabnya ada pada argument criteria_range.
Perlu diingat bahwa:
KRITERIA DALAM RUMUS SUMIFS HARUS TERDIRI DARI 2 ARGUMENT BERPASANGAN (CRITERIA_RANGE DAN CRITERIA)
ROLAN MARDANI
Selain itu, criteria_range juga harus memiliki jumlah baris dan kolom yang sama banyak dengan range pada argument sum_range.
Ini mutlak dan tidak bisa ditawar. Misalnya, Saya mengubah argument criteria_range1 menjadi =SUMIFS(C2:C9,B2: B6,”TOKO A”).
Ini hasilnya:
Rumus SUMIFS menghasilkan Error #VALUE! karena criteria_range hanya terdiri dari 5 baris (row 2 sampai 6). Sementara sum_range terdiri dari 8 baris (row 2 sampai 9).
Penting! Ada 9 jenis pesan error dari Rumus Excel. Ketahui penyebab dan solusinya melalui SUB-BAB Formula Errors.
Yaaah… Saya rasa sudah cukup Panduan Rumus SUMIFS Excel ini. Jika Anda kesulitan menggunakannya atau punya tips lain, silahkan coret-coret kolom komentar.