Fungsi Sumif Dan Rumus Penjumlahan Bersyarat
Sesuai namanya, SUMIF merupakan adonan SUM dan IF, sanggup diartikan “Jumlahkan Jika”. Dengan kata lain fungsi ini dipakai untuk melaksanakan penjumlahan jikalau syarat atau kondisi tertentu terpenuhi. Oleh lantaran itu sering juga diistilahkan sebagai penjumlahan kondisional (conditional sum)
Fungsi SUMIF sanggup dipakai baik pada excel versi usang (2003 atau yang lebih awal) maupun pada excel versi gres (2007,2010,2013 dan 2016). Menarik sekali, jikalau kita sudah memahami rumus SUMIF, maka kita juga akan lebih gampang untuk memahami rumus fungsi lainnya yang sejenis ibarat 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 dipakai untuk melaksanakan operasi penjumlahan bersyarat dengan menurut kriteria atau kondisi tertentu. Adapun Syntax dari fungsi ini yakni 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 dibutuhkan terutama jikalau argumen pertama berisi data text.
Range = Sekelompok sel yang akan dievaluasi , contohnya A1:A10
Criteria = syarat atau kondisi yang harus terpenuhi. Kriteria ini sanggup berbentuk bilangan, text, tanggal, expresi logika, rujukan sel maupun fungsi excel lainnya. contohnya kita sanggup memakai kritera ibarat 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 dibutuhkan tanda petik.
Sum_range = kumpulan sel untuk dijumlahkan jikalau kondisi terpenuhi. Argumen ini bersifat opsional dan jikalau tidak ada maka rumus SUMIF akan menjumlahkan data pada argumen pertama (argumen range)
Untuk memperjelas citra syntax fungsi SUMIF di excel mari kita perhatikan contoh berikut.
Anggaplah anda mempunyai list beberapa produk pada kolom A dan jumlahnya pada Kolom C.
Selanjutnya kita akan mencoba menjumlahkan semua stock produk tertentu, contohnya 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 melaksanakan penjumlahan bersyarat, =SUMIF(A2:A9,"Nanas",C2:C9)
Dikarenakan kriteria “Nanas” disimpan di sel F1, maka rumus di atas sanggup ditulis ulang kembali dengan cara mengganti argumen criteria “Nanas” menjadi rujukan 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 mengakibatkan 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 sanggup menyediakan sel C2 atau C2:C4, atau bahkan C2:C100 sebagai argumen sum_range. Dan karenanya yang diperoleh sama saja.
Satu hal yang harus dipastikan yakni 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 rujukan argumen range
C2 = sel pertama dari rujukan argumen sum_range
Akan tetapi, untuk fasilitas membaca rumus, disarankan untuk mengetikan rujukan sum_range dengan ukuran atau dimensi yang sama dengan rujukan range.
Dalam kaitan contoh diatas, disarankan memakai rujukan C2:C9 sebagai argumen sum_range, dimana rujukan tersebut berdimensi sama dengan rujukan A2:A9 yang merupakan argumen range.
Beberapa Contoh Bagaimana cara Menggunakan SUMIF di Excel
Diharapkan contoh sederhana yang sudah ditampilkan di atas sanggup menunjukkan pemahaman dasar bagaimana fungsi SUMIF bekerja. Berikutnya kita akan menemukan beberapa contoh rumus yang mendemonstrasikan bagaimana SUMIF sanggup bekerja dengan banyak sekali kriteria dan banyak sekali jenis set data yang berbeda.
Fungsi SUMIF dengan Kriteria Lebih Dari, Kurang Dari atau Sama Dengan
Mari kita perhatikan beberapa rumus SUMIF yang sanggup dipakai untuk menjumlahkan bilangan dengan nilai lebih dari (>), kurang dari (<) atau sama dengan (=) nilai kriteria yang ditentukan.
Catatan : Perhatikan bahwa pada rumus SUMIF, kita memakai 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 jikalau nilai bilangan lebih dari 5 |
Jumlahkan Jika Kurang Dari | < | =SUMIF(B2:B10, "<10", C2:C10) | Menjumlahkan bilangan pada sum_range C2:C10 jikalau sel sejajar pada range B2:B10 bernilai kurang dari 10 |
Menjumlahkan jikalau sama dengan | = (Dapat ditiadakan) | =SUMIF(B2:B10, "="&D1) or =SUMIF(B2:B10,D1) | Menjumlahkan bilangan pada range B2:B10 jikalau nilainya sama dengan nilai di sel D1. Argumen kriteria sanggup merujuk pribadi pada referensi. |
Menjumlahkan jikalau tidak sama dengan | <> | =SUMIF(B2:B10, "<>"&D1, C2:C10) | Menjumlahkan bilangan pada sum_range C2:C10 jikalau sel sejajar di range B2:B10 berbeda nilainya dengan sel D1 |
Menjumlahkan jikalau nilai lebih dari atau sama dengan | >= | =SUMIF(B2:B10, ">=5") | Menjumlahkan bilangan pada range B2:B10 jikalau nilainya minimum 5 |
Menjumlahkan jikalau kurang dari atau sama dengan | <= | =SUMIF(B2:B10, "<=10", C2:C10) | Menjumlahkan bilangan pada sum_range C2:C10 jikalau 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, jikalau 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, jikalau sel sejajar pada range A2:A8 mengandung text “pisang” | |
Menjumlahkan jikalau sama dengan | Exact match: =SUMIF(A2:A8, "<>pisang", C2:C8) | Menjumlahkan bilangan pada sum_range C2:C8, jikalau sel sejajar pada range A2:A8 tidak hanya berisi text “pisang”. Makanya jikalau berisi pisang raja, pisang ambon tetap akan terjumlahkan. |
Partial match: =SUMIF(A2:A8, "<>*pisang*", C2:C8) | Menjumlahkan bilangan pada range sel C2:C8, jikalau 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 abjad kecil maupun abjad kapital. Oleh karenanya tidak ada bedanya apakah anda menuliskan “pisang” atau “PISANG”.
Menggunakan Operator Komparasi (Perbandingan) dengan Referensi Sel
Untuk mendapat rumus yang lebih fleksibel maka sebaiknya kita menempatkan kriteria di dalam sel, alih-alih mengetikannya pribadi dalam rumus. Ini bertujuan biar saat 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 rujukan sel biar sanggup dijadikan sebagai argumen kriteria dalam rumus SUMIF, sebagaimana digambarkan dalam screenshot di atas..
=SUMIF(A2:A9,"<>"&F1,C2:C9)
Juga sanggup dituliskan sebagai berikut:
=SUMIF(A2:A9,CONCATENATE("<>",F1),C2:C9)
Untuk budi “sama dengan”, kita sanggup memakai 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 dipakai untuk melaksanakan penjumlahan bersyarat text secara parsial. Maksudnya yakni mengambil pecahan text tertentu sebagai argumen criteria dalam rumus SUMIF, oleh lantaran itu sering disebut dengan istilah partial match.
Berikut jenis-jenis karakter wildcard yang sanggup 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 pecahan awal text kriteria
=SUMIF(A2:A9,"Pisang*",C2:C9)
Penjumlahan bersyarat text “pisang” di pecahan selesai 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 mengakibatkan rumus lebih fleksibel maka sebaiknya kriteria ditempatkan dalam sel. Misalnya di sel H1, sehingga rumus SUMIF menjadi:
Penjumlahan bersyarat partial match di pecahan awal text kriteria
=SUMIF(A2:A9,H1&"*",C2:C9)
Penjumlahan bersyarat text “pisang” di pecahan selesai 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 rujukan dengan karakter wildcard asterisk (*) dalam tanda kutip.
Contoh 2 . Menjumlahkan bilangan dengan syarat sejumlah abjad atau karakter.
Anggaplah kita menetapkan sejumlah karakter sebagai syarat penjumlahan memakai fungsi SUMIF. Misalnya menjumlahkan bilangan pada range C2:C9 jikalau 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 jikalau sel sejajar di kolom C mengandung minimal 1 karakter.
– Menjumlakan bilangan yang ada di Sel C2:C9 jikalau sel sejajar di kolom C mengandung minimal 1 karakter.
=SUMIF(B2:B9,"*", C2:C9)
– Menjumlakan bilangan yang ada di Sel C2:C9 jikalau sel sejajar di kolom B berisi, termasuk string dengan panjang nol yang diperoleh dari rumus lainnya, contohnya =””
– Menjumlakan bilangan yang ada di Sel C2:C9 jikalau sel sejajar di kolom B berisi, termasuk string dengan panjang nol yang diperoleh dari rumus lainnya, contohnya =””
Kedua rumus diatas mengabaikan nilai non text ibarat error, boolean, bilangan dan data.
Contoh 4. Memperlakukan * dan ? sebagai karakter biasa.
Terkadang mungkin kita ingin memakai * 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 menerangkan 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 sanggup lebih gampang difahami.
Sebagai wildcard:
Contoh1 : =SUMIF(B2:B9,"*",C2:C9)
Menghasilkan nilai 7, karena kriteria "*" menerangkan sel di range B2:B9 harus ada minimal satu karakter sehingga sel sejajar di rang C2:C9 sanggup 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 "?" menerangkan sel di range B2:B9 harus terdiri atas satu karakter saja sehingga sel sejajar di range C2:C9 sanggup 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 sanggup 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 sanggup dijumlahkan. Dan sel di range B2:B9 yang memenuhi syarat tersebut ada 2 sel.
Menjumlahkan Bilangan Terbesar Atau Terkecil Dalam Sebuah Range
Pada pecahan ini sedikit melenceng dari pembahasan fungsi SUMIF, tapi masih berafiliasi dengan penjumlahan bersyarat. Untuk menjumlahkan bilangan terbesar atau terkecil dari sebuah range excel maka kita sanggup menggabungkan fungsi SUM dengan fungsi LARGE atau SMALL.
Contoh 1. Menambahkan beberapa bilangan terbesar/terkecil
Untuk menambahkan beberapa bilangan terbesar, maka anda sanggup mengetiknya pribadi 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 sanggup memakai formula array memakai 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 sanggup memakai 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 sehabis mengetikan formula.
Contoh 3. Menjumlahkan bilangan terbesar atau terkecil dengan banyak bilangan yang bervariasi.
Jika anda termasuk orang yang tidak nyaman saat 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 dibentuk memakai rujukan sel tersbut.
Contohnya: untuk menjumlahkan 10 bilangan terbesar pada range A1:A15, jikalau angka 10 disimpan di sel C1 maka sanggup dituliskan rumus sebagai berikut:
=SUM(LARGE(A1:A15,(ROW(INDIRECT("1:"&C1)))))
Untuk menjumlahkan 10 bilangan terkecil pada range A1:A15, jikalau angka 10 disimpan di sel C2 maka sanggup dituliskan rumus sebagai berikut:
=SUM(SMALL(A1:A15,(ROW(INDIRECT("1:"&C2)))))
Karena merupakan rumus array, maka jangan lupa untuk menekan CTR+SHIFT+ENTER sehabis mengetikan rumus tersebut untuk mendapat 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 jikalau sel sejajar di range A2:A10 benar-benar kosong.
=SUMIF(A2:A10,"=",C2:C10)
Sel Bernilai Blank/kosong: meliputi kosong jenis pertama dan kosong yang disebabkan oleh formula, contohnya 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 jikalau 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 contoh termasuk sel kosong yang diperoleh dari formula (zero length string)
Rumus SUMIF untuk Penjumlahan Bersyarat Sel Sejajar Bersisi Data Tanggal
Cara memakai rumus SUMIF untuk penjumlahan bersyarat tanggal, intinya sama saja dengan penggunaan fungsi SUMIF bersyarat type data lainnya. Dalam hal ini, kita juga sanggup memakai operator komparasi (perbandingan) sebagaimana sudah dijelaskan pada sebuah sub pecahan sebelumnya.
Seandainya kita ingin menjumlahkan bilangan bedasarkan tanggal sekarang, maka kita sanggup memakai kombinasi fungsi SUMIF dengan TODAY sebagaimana contoh berikut:
Kriteria | Contoh Formula |
Menjumlahkan nilai menurut tanggal sekarang | =SUMIF(A2:A10, TODAY(), C2:C10) |
Menjumlahkan nilai sebelum tanggal sekarang | =SUMIF(A2:A10, "<"&TODAY(), C2:C10) |
Menjumlahkan nilai sehabis 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 yakni mengunakan kombinasi antara 2 fungsi SUMIF.
Misalnya: untuk menjumlahkan nilai yang ada di sel C2:C100 jikalau kolom A berisi tanggal antara mulai 1 Desember 2016 s.d 31 Desember 2016, maka rumus sanggup 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 jikalau diperhatikan dengan lebih seksama, sesungguhnya rumus tersebut sangat simple.
Fungsi SUMIF pertama dipakai 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 memakai fungsi SUMIF ke-2
Bagaimana Menjumlahkan Nilai Pada Beberapa Kolom.
Untuk memahami duduk masalah dengan lebih baik, mari kita perhatikan dengan seksama contoh berikut:
Anggaplah anda mempunyai sebuah tabel berisi data penjualan bulanan beberapa produk dalam beberapa bulan.
Pertanyaannya: bagaimana anda sanggup mendapat 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 yakni dengan memakai 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 sanggup memakai rumus SUMIF ibarat biasa
=SUMIF(A2:A10,"apel", F2:F10)
Dan jikalau kriteria apel disimpan di sel A14, maka rumus tersebut sanggup dituliskan:
=SUMIF(A2:A10,A14,F2:F10)
Jika anda lebih suka melaksanakan penjumlahan bersyarat tanpa memakai kolom bantu, maka anda sanggup memakai 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 yakni memakai fungsi SUMPRODUCT, ibarat contoh berikut:
=SUMPRODUCT((A2:A10=A14)*C2:E10)
Atau memakai 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 lantaran type data di sel atau beberapa argumen tidak cocok. Berikut yakni beberapa hal yang harus dicek untuk memastikan fungsi SUMIF sanggup 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 rujukan rang ibarat A1:A10. Jika anda mencoba dengan lainnya contohnya memakai array ibarat {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 sanggup memakai rujukan 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 berjulukan Book1 jikalau 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 saat book1 ditutup atau close. Ini terjadi lantaran range yang dijadikan rujukan oleh rumus SUMIF, di rujukan 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 mempunyai 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 mempunyai ukuran yang sama. Di exel 2000 atau versi yang lebih awal, ukuran yang berbeda akan mengakibatkan problem. Ini juga kata orang ya, lantaran saya belum pernah mencobanya di excel 2000.
Akan tetapi, meskipun excel versi terbaru ibarat excel 2010, 2013 dan 2016, rumus SUMIF yang lebih komplek jikalau sum_range berbeda jumlah barisnya dengan argumen range, maka kebanyakan user perlu extra lebih untuk memahaminya.
Oleh lantaran itu secara praktisnya yakni selalu memakai 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:
0 Response to "Fungsi Sumif Dan Rumus Penjumlahan Bersyarat"
Posting Komentar