Fungsi SUMIF dan Rumus Penjumlahan Bersyarat
Sesuai namanya, SUMIF merupakan gabungan SUM dan IF, dapat diartikan “Jumlahkan Jika”. Dengan kata lain fungsi ini digunakan untuk melakukan penjumlahan jika syarat atau kondisi tertentu terpenuhi. Oleh karena itu sering juga diistilahkan sebagai penjumlahan kondisional (conditional sum)
Fungsi SUMIF dapat digunakan baik pada excel versi lama (2003 atau yang lebih awal) maupun pada excel versi baru (2007,2010,2013 dan 2016). Menarik sekali, jika kita sudah memahami rumus SUMIF, maka kita juga akan lebih mudah untuk memahami rumus fungsi lainnya yang sejenis seperti SUMIFS, COUNTIF, COUNTIFS dan AVERAGEIF.
- Rumus Fungsi Excel SUMIF - Syntax dan Cara Penggunaan
- Contoh-Contoh Rumus Excel SUMIF
- Penjumlahan Beryarat Bilangan Lebih Rendah, Lebih Tinggi atau Sama Dengan.
- Rumus SUMIF dengan Syarat Kriteria Text
- Rumus SUMIF Menggunakan Operator Komparasi Dengan Referensi Sel
- Contoh-Contoh Rumus SUMIF Menggunakan Karakter Wildcard
- Rumus SUMIF Untuk Menjumlahkan Bilangan Terbesar Atau Terkecil
- Rumus SUMIF Untuk Penjumlahan Bersyarat Terkait Sel Kosong/Tidak Kosong
- Rumus SUMIF Untuk Penjumlahan Bersyarat dengan Kriteria Tanggal
- Rumus SUMIF Untuk Penjumlahan Bilangan Pada Beberapa Kolom
- Solusi Masalah Rumus SUMIF Error, tidak Bekerja atau Tidak Berfungsi dengan Benar
Syntax Dan Cara Penggunaan Fungsi SUMIF di Excel
Fungsi SUMIF digunakan untuk melakukan operasi penjumlahan bersyarat dengan berdasarkan kriteria atau kondisi tertentu. Adapun Syntax dari fungsi ini adalah sebagai berikut:
SUMIF(range, criteria, [sum_range])
Dari ketiga argumen fungsi SUMIF tersebut, Argumen pertama (range) dan kedua (criteria) bersifat wajib, sedangkan argumen yang ketiga (sum_range) bersifat opsional atau boleh diabaikan. Akan tetapi, meskipun bersifat opsional, argumen ke-3 penting dan sering diperlukan terutama jika argumen pertama berisi data text.
Range = Sekelompok sel yang akan dievaluasi , misalnya A1:A10
Criteria = syarat atau kondisi yang harus terpenuhi. Kriteria ini bisa berbentuk bilangan, text, tanggal, expresi logika, referensi sel maupun fungsi excel lainnya. misalnya kita dapat menggunakan kritera seperti 3, 10, 0, “jeruk”, “10/12/2016””, “<4”, “B2” dan sebagainya.
Perlu diingat! Kriteria text yang mengandung simbol matematika harus diawali dan diakhiri dengan tanda petik 2 ("). Sedangkan untuk kriteria numerik, tidak diperlukan tanda petik.
Sum_range = kumpulan sel untuk dijumlahkan jika kondisi terpenuhi. Argumen ini bersifat opsional dan jika tidak ada maka rumus SUMIF akan menjumlahkan data pada argumen pertama (argumen range)
Untuk memperjelas gambaran syntax fungsi SUMIF di excel mari kita perhatikan contoh berikut.
Anggaplah anda memiliki list beberapa produk pada kolom A dan jumlahnya pada Kolom C.
Selanjutnya kita akan mencoba menjumlahkan semua stock produk tertentu, misalnya nanas:
Perhatikan gambar di atas dengan seksama dan selanjutnya mari kita definisikan masing masing argumen untuk fungsi SUMIF
range - A2:A9
criteria - "Nanas"
sum_range - C2:C9
Kemudian susunlah parameter-parameter tersebut di dalam fungsi SUMIF untuk melakukan penjumlahan bersyarat, =SUMIF(A2:A9,"Nanas",C2:C9)
Dikarenakan kriteria “Nanas” disimpan di sel F1, maka rumus di atas dapat ditulis ulang kembali dengan cara mengganti argumen criteria “Nanas” menjadi referensi sel F1.
=SUMIF(A2:A9,"F1",C2:C9)
Penting diketahui: Dalam prakteknya, sum_range tidak harus sama ukuran atau dimensinya dengan argumen range. Fungsi SUMIF akan menjadikan sel pertama paling atas kiri dari dari argumen sum_range kemudian menghitung dengan mengambil ukuran dimensi yang sama dengan argumen range.
Dengan memperhatikan kembali contoh rumus SUMIF diatas, maka kita dapat menyediakan sel C2 atau C2:C4, atau bahkan C2:C100 sebagai argumen sum_range. Dan hasilnya yang diperoleh sama saja.
Satu hal yang harus dipastikan adalah sel pertama dari argumen sum_range harus sejajar dengan sel pertama dari argumen range.
Sebagaimana contoh diatas, sel yang sejajar dari kedua argumen tersebut adalah
A2 = sel pertama dari referensi argumen range
C2 = sel pertama dari referensi argumen sum_range
Akan tetapi, untuk kemudahan membaca rumus, disarankan untuk mengetikan referensi sum_range dengan ukuran atau dimensi yang sama dengan referensi range.
Dalam kaitan contoh diatas, disarankan menggunakan referensi C2:C9 sebagai argumen sum_range, dimana referensi tersebut berdimensi sama dengan referensi A2:A9 yang merupakan argumen range.
Beberapa Contoh Bagaimana cara Menggunakan SUMIF di Excel
Diharapkan contoh sederhana yang sudah ditampilkan di atas dapat memberikan pemahaman dasar bagaimana fungsi SUMIF bekerja. Berikutnya kita akan menemukan beberapa contoh rumus yang mendemonstrasikan bagaimana SUMIF dapat bekerja dengan berbagai kriteria dan berbagai jenis set data yang berbeda.
Fungsi SUMIF dengan Kriteria Lebih Dari, Kurang Dari atau Sama Dengan
Mari kita perhatikan beberapa rumus SUMIF yang dapat digunakan untuk menjumlahkan bilangan dengan nilai lebih dari (>), kurang dari (<) atau sama dengan (=) nilai kriteria yang ditentukan.
Catatan : Perhatikan bahwa pada rumus SUMIF, kita menggunakan operator komparasi (operator perbandingan) diikuti bilangan atau text yang berada dalam tanda petik dua ("")
Criteria | Operator | Contoh Rumus | Keterangan |
Jumlahkan Jika Lebih Dari | > | =SUMIF(B2:B10, ">5") | Menjumlahkan bilangan pada kumpulan sel B2:B10 jika nilai bilangan lebih dari 5 |
Jumlahkan Jika Kurang Dari | < | =SUMIF(B2:B10, "<10", C2:C10) | Menjumlahkan bilangan pada sum_range C2:C10 jika sel sejajar pada range B2:B10 bernilai kurang dari 10 |
Menjumlahkan jika sama dengan | = (Dapat ditiadakan) | =SUMIF(B2:B10, "="&D1) or =SUMIF(B2:B10,D1) | Menjumlahkan bilangan pada range B2:B10 jika nilainya sama dengan nilai di sel D1. Argumen kriteria bisa merujuk langsung pada referensi. |
Menjumlahkan jika tidak sama dengan | <> | =SUMIF(B2:B10, "<>"&D1, C2:C10) | Menjumlahkan bilangan pada sum_range C2:C10 jika sel sejajar di range B2:B10 berbeda nilainya dengan sel D1 |
Menjumlahkan jika nilai lebih dari atau sama dengan | >= | =SUMIF(B2:B10, ">=5") | Menjumlahkan bilangan pada range B2:B10 jika nilainya minimum 5 |
Menjumlahkan jika kurang dari atau sama dengan | <= | =SUMIF(B2:B10, "<=10", C2:C10) | Menjumlahkan bilangan pada sum_range C2:C10 jika sel sejajar di range B2:B10 bernilai maksimum 10. |
Bagaimana Cara Menggunakan Fungsi SUMIF Excel dengan Kriteria Berupa Text
Selain syarat bilangan, fungsi SUMIF juga memungkinkan penggunanya untuk menambahkan nilai tergantung syarat text pada sel sejajar di kolom lainnya.
Silahkan diperhatikan kembali contoh-contoh rumus SUMIF berikut untuk menjumlahkan dengan syarat text persis sama (exact match) atau secara sama sebagian (partial match).
Criteria | Contoh Rumus | Keterangan |
Menjumlahkan Jika Sama Dengan | Exact match: =SUMIF(A2:A8, "pisang", C2:C8) | Menjumlahkan bilangan pada sum_range C2:C8, jika sel sejajar pada range A2:A8 hanya berisi text “pisang” |
Partial match: =SUMIF(A2:A8, "*pisang*", C2:C8) | Menjumlahkan bilangan pada sum_range C2:C8, jika sel sejajar pada range A2:A8 mengandung text “pisang” | |
Menjumlahkan jika sama dengan | Exact match: =SUMIF(A2:A8, "<>pisang", C2:C8) | Menjumlahkan bilangan pada sum_range C2:C8, jika sel sejajar pada range A2:A8 tidak hanya berisi text “pisang”. Makanya jika berisi pisang raja, pisang ambon tetap akan terjumlahkan. |
Partial match: =SUMIF(A2:A8, "<>*pisang*", C2:C8) | Menjumlahkan bilangan pada range sel C2:C8, jika sel sejajar pada range A2:A8 tidak mengandung text “pisang” sama sekali. |
Selanjutya mari kita lihat penjumlahan secara persis (eksak) dengan kriteria “Jumlahkan Jika Tidak Sama Dengan”. Sebagaimana pada screenshot berikut, untuk menjumlahkan jumlah stock semua produk selain “ pisang raja”
=SUMIF(A2:A9,"<>Pisang Raja",C2:C9)
Catatan: Seperti kebanyakan fungsi Excel lainnya, SUMIF juga besifat case-insensitive artinya tidak membedakan huruf kecil maupun huruf kapital. Oleh karenanya tidak ada bedanya apakah anda menuliskan “pisang” atau “PISANG”.
Menggunakan Operator Komparasi (Perbandingan) dengan Referensi Sel
Untuk mendapatkan rumus yang lebih fleksibel maka sebaiknya kita menempatkan kriteria di dalam sel, alih-alih mengetikannya langsung dalam rumus. Ini bertujuan supaya ketika ada perubahan kriteria maka kita tidak perlu repot mengganti rumus, tapi cukup dengan mengganti kriteria pada sel yang menjadi referensi.
Catatan : Gunakan operator ampersand (&) atau fungsi CONCATENATE untuk menggabungkan operator komparasi dan referensi sel supaya dapat dijadikan sebagai argumen kriteria dalam rumus SUMIF, sebagaimana digambarkan dalam screenshot di atas..
=SUMIF(A2:A9,"<>"&F1,C2:C9)
Juga dapat dituliskan sebagai berikut:
=SUMIF(A2:A9,CONCATENATE("<>",F1),C2:C9)
Untuk logika “sama dengan”, kita bisa menggunakan operator “=” ataupun tidak, sehingga kedua contoh formula berikut akan menghasilkan nilai yang sama.
Formula 1: =SUMIF(A2:A8, "="&F1, C2:C8)
Formula 2: =SUMIF(A2:A8, F1, C2:C8)
Cara Menggunakan Rumus SUMIF dengan Karakter Wildcard
Karakter wildcard digunakan untuk melakukan penjumlahan bersyarat text secara parsial. Maksudnya adalah mengambil bagian text tertentu sebagai argumen criteria dalam rumus SUMIF, oleh karena itu sering disebut dengan istilah partial match.
Berikut jenis-jenis karakter wildcard yang dapat digunakan
- Asterik (*) = memakili sejumlah karakter
- Tanda Tanya (?) = mewakili karakter tunggal pada posisi spesifik
Contoh 1 : Menjumlahkan nilai dengan kriteria text parsial
Anggaplah, kita akan menjumlah kan sesuatu yang terkait dengan pisang.Tidak peduli jenisnya atau bendanya apa, yang penting mengandung kata “pisang”.
Penjumlahan bersyarat text “pisang” di bagian awal text kriteria
=SUMIF(A2:A9,"Pisang*",C2:C9)
Penjumlahan bersyarat text “pisang” di bagian akhir text kriteria
=SUMIF(A2:A9,"*Pisang",C2:C9)
Penjumalahan bersyarat text pisang di sembarang posisi dalam text kriteria
=SUMIF(A2:A9,"*Pisang*",C2:C9)
Perhatikan ilustrasi berikut:
Dan untuk menjadikan rumus lebih fleksibel maka sebaiknya kriteria ditempatkan dalam sel. Misalnya di sel H1, sehingga rumus SUMIF menjadi:
Penjumlahan bersyarat partial match di bagian awal text kriteria
=SUMIF(A2:A9,H1&"*",C2:C9)
Penjumlahan bersyarat text “pisang” di bagian akhir text kriteria
=SUMIF(A2:A9,"*"&H1,C2:C9)
Penjumalahan bersyarat text pisang di sembarang posisi dalam text kriteria
=SUMIF(A2:A9,"*"&H1&"*",C2:C9
Perhatikan kembali contoh diatas: gunakan operator ampersand (&) untuk menggabungkan referensi dengan karakter wildcard asterisk (*) dalam tanda kutip.
Contoh 2 . Menjumlahkan bilangan dengan syarat sejumlah huruf atau karakter.
Anggaplah kita menetapkan sejumlah karakter sebagai syarat penjumlahan menggunakan fungsi SUMIF. Misalnya menjumlahkan bilangan pada range C2:C9 jika sel sejajar pada range B2:A9 mengandung text dengan panjang 5 karakter:
=SUMIF(B2:B9, "?????", C2:C9)
Contoh 3 : Menjumlahkan sel yang berkesesuaian dengan text tertentu
Jika kita bekerja dengan beberapa type data dalam lembar kerja excel, kemudian kita ingin menjumlahkan beberapa bilangan, anggaplah dalam sebuah sum_range dengan syarat sel sejajar di range lainnya.
=SUMIF(B2:B9,"?*", C2:C9)
– Menjumlakan bilangan yang ada di Sel C2:C9 jika sel sejajar di kolom C mengandung minimal 1 karakter.
– Menjumlakan bilangan yang ada di Sel C2:C9 jika sel sejajar di kolom C mengandung minimal 1 karakter.
=SUMIF(B2:B9,"*", C2:C9)
– Menjumlakan bilangan yang ada di Sel C2:C9 jika sel sejajar di kolom B berisi, termasuk string dengan panjang nol yang diperoleh dari rumus lainnya, misalnya =””
– Menjumlakan bilangan yang ada di Sel C2:C9 jika sel sejajar di kolom B berisi, termasuk string dengan panjang nol yang diperoleh dari rumus lainnya, misalnya =””
Kedua rumus diatas mengabaikan nilai non text seperti error, boolean, bilangan dan data.
Contoh 4. Memperlakukan * dan ? sebagai karakter biasa.
Terkadang mungkin kita ingin menggunakan * dan ? sebagai karakter biasa, bukan sebagai wildcard, maka gunakanlan tanda gelombang / tidle (~) sebelum tanda * atau ?.
=SUMIF(A2:A8, "~?", C2:C8)
Perhatikan screenshot diatas. Contoh ke-1 s.d ke-2 menunjukan cara penggunaan asterisk (*) dan tanda tanya (?) sebagai karakter wildcard. Sedangkan contoh ke-3 dan ke-4 menggabarkan cara penggunaan kedua karakter tersebut sebagai karakter biasa.
Mari kita jelaskan contoh-contoh tersebut. Perhatikan range B2:B9, penggunaan angka 1 hanya contoh saja untuk menyederhanakan perhitungan penjumlahan sehingga dapat lebih mudah difahami.
Sebagai wildcard:
Contoh1 : =SUMIF(B2:B9,"*",C2:C9)
Menghasilkan nilai 7, karena kriteria "*" menunjukan sel di range B2:B9 harus ada minimal satu karakter sehingga sel sejajar di rang C2:C9 dapat dijumlahkan. Dan sel di range B2:B9 yang memenuhi syarat tersebut ada 7 sel.
Contoh2 : =SUMIF(B2:B9,"?",C2:C9)
Menghasilkan nilai 5, karena kriteria "?" menunjukan sel di range B2:B9 harus terdiri atas satu karakter saja sehingga sel sejajar di range C2:C9 dapat dijumlahkan. Dan sel di range B2:B9 yang memenuhi syarat tersebut ada 5 sel.
Sebagai karakter biasa
Contoh3 : =SUMIF(B2:B9,"~*",C2:C9)
Menghasilkan nilai 1, karena kriteria "~*" menunjukan sel di range B2:B9 harus hanya berisi text "*" saja sehingga sel sejajar di range C2:C9 dapat dijumlahkan. Dan sel di range B2:B9 yang memenuhi syarat tersebut hanya ada 1 sel.
Contoh4 : =SUMIF(B2:B9,"~?",C2:C9)
Menghasilkan nilai 2, karena kriteria "~?" menunjukan sel di range B2:B9 harus hanya berisi text "?" saja sehingga sel sejajar di range C2:C9 dapat dijumlahkan. Dan sel di range B2:B9 yang memenuhi syarat tersebut ada 2 sel.
Menjumlahkan Bilangan Terbesar Atau Terkecil Dalam Sebuah Range
Pada bagian ini sedikit melenceng dari pembahasan fungsi SUMIF, tapi masih berhubungan dengan penjumlahan bersyarat. Untuk menjumlahkan bilangan terbesar atau terkecil dari sebuah range excel maka kita dapat menggabungkan fungsi SUM dengan fungsi LARGE atau SMALL.
Contoh 1. Menambahkan beberapa bilangan terbesar/terkecil
Untuk menambahkan beberapa bilangan terbesar, maka anda dapat mengetiknya langsung di formula, misalnya:
Menjumlahkan 5 bilangan terbesar pada range A1:A10
=SUM(LARGE(A1:A10,{1,2,3,4,5}))
Demikian juga untuk menumlahkan beberapa bilangan terkecil
Menjumlahkan 5 bilangan terkecl pada range A1:A10
=SUM(SMALL(A1:A10,{1,2,3,4,5}))
Contoh 2. Menambahkan Lebih Banyak Bilangan Teratas atau Terbawah
Pada contoh pertama, kita sudah mengetahui cara menjumlahkan 5 bilangan terbesar dan terkecil dengan cara mengetikan {1,2,3,4,5} dalam argumen fungsi LARGE. Cara tersebut cocok dilakukan untuk data yang sedikit dan batasan terbesar dan terkecil nya juga sedikit.
Untuk data yang lebih besar dan batasan terbesar dan tekecilnya banyak, maka kita dapat menggunakan formula array menggunakan fungsi SUM, LARGE dan menambahkan fungsi ROW dan INDIRECT untuk mendefinisikan kriteria terbesar/terkecilnya.
Misalnya: Untuk menjumlahkan 10 bilangan terbesar pada range A1:A15, maka kita dapat menggunakan rumus sebagai berikut:
=SUM(LARGE(A1:A15,(ROW(INDIRECT("1:10")))))
Dan untuk menjumlahkan 10 bilangan terkecil, maka gunakan rumus berikut:
=SUM(SMALL(A1:A15,(ROW(INDIRECT("1:10")))))
Karena rumus tersebut merupakan rumus array, maka jangan lupa untuk menekan CTR + Shift + Enter setelah mengetikan formula.
Contoh 3. Menjumlahkan bilangan terbesar atau terkecil dengan banyak bilangan yang bervariasi.
Jika anda termasuk orang yang tidak nyaman ketika harus merubah formula setiap kali ada perubahan berapa bilangan terbesar dan terkecil untuk dijumlahkan, maka sebaiknya batasan berapa bilangannya di simpan di dalam sel, kemudian rumus penjumlahan bilangan terbesar/terkecil dibuat menggunakan referensi sel tersbut.
Contohnya: untuk menjumlahkan 10 bilangan terbesar pada range A1:A15, jika angka 10 disimpan di sel C1 maka dapat dituliskan rumus sebagai berikut:
=SUM(LARGE(A1:A15,(ROW(INDIRECT("1:"&C1)))))
Untuk menjumlahkan 10 bilangan terkecil pada range A1:A15, jika angka 10 disimpan di sel C2 maka dapat dituliskan rumus sebagai berikut:
=SUM(SMALL(A1:A15,(ROW(INDIRECT("1:"&C2)))))
Karena merupakan rumus array, maka jangan lupa untuk menekan CTR+SHIFT+ENTER setelah mengetikan rumus tersebut untuk mendapatkan hasil yang benar.
Rumus SUMIF Untuk Penjumlahan Bersyarat Sel Sejajar Yang Kosong / Tidak Kosong
Ada 2 jenis sel kosong yaitu:
Sel kosong mutlak, artinya tidak ada apapun di dalam sel tersebut, termasuk formula yang menghasilkan string kosong. Gunakan kriteria "=" untuk sel kosong jenis ini.
Contoh: Rumus berikut untuk menjumlahkan bilangan di range C2:C10 jika sel sejajar di range A2:A10 benar-benar kosong.
=SUMIF(A2:A10,"=",C2:C10)
Sel Bernilai Blank/kosong: mencakup kosong jenis pertama dan kosong yang disebabkan oleh formula, misalnya formula ="". Untuk mendefinisikan sel kosong jenis ini maka gunakan kriteria "" dalam rumus SUMIF.
=SUMIF(A2:A10,"",C2:C10)
Kedua contoh formula diatas mengevaluasi sel-sel yang berada pada kolom range A2:A10, kemudian jika merupakan sel kosong, maka sel lain dengan baris yang sama di range C2:C10 akan dijumlahkan.
Jika anda bermaksud untuk menjumlahkan nilai sel di kolom C yang sejajar dengan sel di kolom A yang tidak kosong maka gunakan “<>” sebagai kriteria di rumus SUMIF, contohnya:
=SUMIF(A2:A10,"<>",C2:C10)
Formula diatas menghitung nilai dari sel yang sejajar dengan sel tidak kosong yang dijadikan acuan termasuk sel kosong yang diperoleh dari formula (zero length string)
Rumus SUMIF untuk Penjumlahan Bersyarat Sel Sejajar Bersisi Data Tanggal
Cara menggunakan rumus SUMIF untuk penjumlahan bersyarat tanggal, pada dasarnya sama saja dengan penggunaan fungsi SUMIF bersyarat type data lainnya. Dalam hal ini, kita juga dapat menggunakan operator komparasi (perbandingan) sebagaimana sudah dijelaskan pada sebuah sub bab sebelumnya.
Seandainya kita ingin menjumlahkan bilangan bedasarkan tanggal sekarang, maka kita dapat menggunakan kombinasi fungsi SUMIF dengan TODAY sebagaimana contoh berikut:
Kriteria | Contoh Formula |
Menjumlahkan nilai berdasarkan tanggal sekarang | =SUMIF(A2:A10, TODAY(), C2:C10) |
Menjumlahkan nilai sebelum tanggal sekarang | =SUMIF(A2:A10, "<"&TODAY(), C2:C10) |
Menjumlahkan nilai setelah tanggal sekarang | =SUMIF(A2:A10, ">"&TODAY(), C2:C10) |
Menjumlahkan nilai di 7 hari kemudian. | =SUMIF(A2:A10, "="&TODAY()+7, C2:C9) |
Bagaimana cara menjumlahkan nilai sesuai rentang tanggal tertentu.
Mungkin anda pernah bertanya. Bagaimana Cara Menjumlahkan data antara 2 tanggal?
Jawabannya adalah mengunakan kombinasi antara 2 fungsi SUMIF.
Misalnya: untuk menjumlahkan nilai yang ada di sel C2:C100 jika kolom A berisi tanggal antara mulai 1 Desember 2016 s.d 31 Desember 2016, maka rumus dapat dituliskan sebagai berikut:
=SUMIF(C2:C100, ">=12/1/2016", C2:C100) - SUMIF(A2:A100, ">12/31/2016", C2:C100)
Formula tersebut mungkin kelihatan sedikit lebih ribet, Namun jika diperhatikan dengan lebih seksama, sebenarnya rumus tersebut sangat simple.
Fungsi SUMIF pertama digunakan untuk menjumlahkan nilai di sel C2:C100 dimana sel berkesesuaian di kolom A lebih dari atau sama dengan tanggal pertama. Kemudian anda kurangkan dengan nilai yang lebih dari tanggal kedua yang diperoleh menggunakan fungsi SUMIF ke-2
Bagaimana Menjumlahkan Nilai Pada Beberapa Kolom.
Untuk memahami masalah dengan lebih baik, mari kita perhatikan dengan seksama contoh berikut:
Anggaplah anda memiliki sebuah tabel berisi data penjualan bulanan beberapa produk dalam beberapa bulan.
Pertanyaannya: bagaimana anda bisa mendapatkan total produk buah tertentu yang terjual di semua wiayah dalam waktu 3 bulan terakhir?
Mengingatkan lagi bahasan sebelumnya, bahwa dimensi argumen sum_range ditentukan oleh dimensi argumen range. Itulah mengapa rumus berikut tidak akan bekerja sesuai harapan.
=SUMIF(A2:A10,"apel",C2:E10)
Mengapa? Karena rumus tersebut hanya akan menjumlahkan data penjualan apel hanya di kolom C.
Solusi paling sederhana dan logis adalah dengan menggunakan kolom bantu berisi total data beberapa bulan di masing-masing baris.
Cukup masukan rumus SUM pada sel F2, kemudian copy ke baris berikutnya di kolom F
Setelah itu anda dapat menggunakan rumus SUMIF seperti biasa
=SUMIF(A2:A10,"apel", F2:F10)
Dan jika kriteria apel disimpan di sel A14, maka rumus tersebut dapat dituliskan:
=SUMIF(A2:A10,A14,F2:F10)
Jika anda lebih suka melakukan penjumlahan bersyarat tanpa menggunakan kolom bantu, maka anda dapat menggunakan beberapa rumus SUMIF terpisah, kemudian menjumlahkan nya menggunaan operator tambah (+) atau fungsi SUM, sesuai contoh berikut:
=SUMIF(A2:A10,A14,C2:C10)+SUMIF(A2:A10,A14,D2:D10)+SUMIF(A2:A10,A14,E2:E10)
Atau
=SUM(SUMIF(A2:A10,A14,C2:C10),SUMIF(A2:A10,A14,D2:D10),SUMIF(A2:A10,A14,E2:E10))
Alternative cara lainnya adalah menggunakan fungsi SUMPRODUCT, seperti contoh berikut:
=SUMPRODUCT((A2:A10=A14)*C2:E10)
Atau menggunakan rumus array
{=SUM((A2:A10=A14)*C2:E10)}
Keempat rumus diatas, sama-sama akan menghasilkan 2.280.000
Sering Ditanyakan : Mengapa Rumus SUMIF Tidak Bekerja/Berfungsi
Ada beberapa alasan kenapa rumus SUMIF tidak bekerja atau tidak menghasilkan perhitungan yang benar. Kadang-kadang nilai yang dihasilkan rumus SUMIF tidak sesuai dengan yang diharapkan karena type data di sel atau beberapa argumen tidak cocok. Berikut adalah beberapa hal yang harus dicek untuk memastikan fungsi SUMIF dapat bekerja dengan benar.
1. Parameter range dan sum_range harus berupa range, bukan array.
Parameter pertama (range) dan parameter kedua (sum_range) dalam rumus SUMIF harus berupa referensi rang seperti A1:A10. Jika anda mencoba dengan lainnya misalnya menggunakan array seperti {1,2,3} maka akan terjadi error.
Rumus yang benar: =SUMIF(A1:A3, "apel", C1:C3)
Rumus yang salah: =SUMIF({1,2,3}, "apel", C1:C3)
2. Cara untuk menjumlahkan nilai dari sheet lain atau workbook lain
Sebagaimana hampir semua function excel lainnya, fungsi SUMIF juga bisa menggunakan referensi sheet lain atau bahkan file exel lainnya yang berbeda.
Sebagai contoh, rumus berikut dimaksudkan untuk menjumlahkan nilai di sel F2:F9 di sheet 1, dari file excel bernama Book1 jika sel berkesesuian di kolom A, dengan nama sheet “apel”.
=SUMIF([Book1.xlsx]Sheet1!$A$2:$A$9,"apel",[Book1.xlsx]Sheet1!$F$2:$F$9)
Akan tapi rumus tersebut tidak akan bekerja ketika book1 ditutup atau close. Ini terjadi karena range yang dijadikan referensi oleh rumus SUMIF, di referensi balik menjadi array. Dan dikarenakan array tidak diizinkan pada rumus sumif, maka rumus tersebut akan menghasilkan error #VALUE!
3. Untuk menghindari problem, pastikan parameter sum dan sum_range memiliki ukuran jumlah baris dan kolom yang sama.
Sebagaimana disampaikan pada permulaan bahasan ini. Pada versi excel yang lebih moderen, parameer sum dan sum_range tidak harus memiliki ukuran yang sama. Di exel 2000 atau versi yang lebih awal, ukuran yang berbeda akan menyebabkan problem. Ini juga kata orang ya, karena saya belum pernah mencobanya di excel 2000.
Akan tetapi, meskipun excel versi terbaru seperti excel 2010, 2013 dan 2016, rumus SUMIF yang lebih komplek jika sum_range berbeda jumlah barisnya dengan argumen range, maka kebanyakan user perlu extra lebih untuk memahaminya.
Oleh karena itu secara praktisnya adalah selalu menggunakan ukuran yang sama pada parameter range dan sum_range
Demikian pembahasan dasar-dasar penggunaan fungsi SUMIF untuk penjumlahan bersyarat dengan kriteria bilangan, kriteria text dan karakter wildcard, serta kriteria tanggal.
Semoga bermanfaat.
Artikel Terkait:
- Fungsi SUM dan Rumus Penjumlahan Pada Excel
- Rahasia Cara Cepat AutoSUM
- Rumus Excel Tidak Berfungsi? Inilah Penyebabnya!
- Memahami Rumus VLOOKUP Dengan Studi Kasus
Referensi:
Posting Komentar untuk "Fungsi SUMIF dan Rumus Penjumlahan Bersyarat"