Featured Post

Lookup Gambar Dengan INDEX MATCH

Apakah Pencarian Gambar bisa dilakukan di excel? Pertanyaan ini sangat menarik sekali untuk dibahas. Jika pembaca mengikuti blog ini, pada ...

Tuesday, December 13, 2016

Fungsi SUMIF dan Rumus Penjumlahan Bersyarat

Fungsi SUMIF Excel
Belajar excel kali ini dimaksudkan untuk membahas tehnik penjumlahan bersyarat menggunakan fungsi SUMIF. Penjelasan diupayakan sedetail dan selengkap mungkin disertai contoh- contoh praktikal rumus SUMIF dengan kriteria bilangan, text, tanggal dan karakter wildcard. Pada bagian akhir akan dibahas bagaimana mengatasi beberapa permasalahan dan error seputar penggunaan fungsi SUMIF.

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:


contoh rumus sumif excel


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)


rumus sumif penjumlahan bersyarat



Contoh rumus excel tersebut mendemonstrasikan penggunaan fungsi SUMIF yang paling sederhana dengan kriteria text nama buah nanas.

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.

cara menggunakan rumus SUMIF


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)


Rumus SUMIF syarat TEXT


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.


Rumus SUMIF dengan operator komparasi



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:

Fungsi SUMIF dengan karakter wildcard

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



Fungsi SUMIF Referensi Kriteria


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)


Fungsi SUMIF Wildcard Sejumlah Karakter






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.

=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 =””

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)


Fungsi SUMIF Wildcard Karakter Biasa



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})) 


Fungsi SUMIF Menjumlahkan Bilangan Terbesar




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")))))

Fungsi SUMIF Penjumlahan Bilangan Terbawah


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.

Cara Menjumlahkan Bilangan Terbawah


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.

Fungsi SUMIF Tanggal



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)}


Fungsi SUMIF Beberapa Kolom


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:






Referensi:


19 comments:

  1. Selamat Sore saya mau nanya bagaimana kalo Data ada di Sheet 1 namun saya ingin membuat perhitungannya di Sheet 2?
    pakai Rumus apa ya?

    ReplyDelete
    Replies
    1. Sebenarnya prinsip kerjanya sama saja baik di sheet yang sama maupun sheet berbeda, hanya saja jika rumus di sheet lain, maka rumusnya akan lebih panjang karena referensi mencantumkan nama sheet

      Delete
  2. Terima kasih ilmunya, sangat bermanfaat, runtut...jelas dan lengkap

    ReplyDelete
  3. Selamat Siang, maaf sebelumnya saya mau tanya, cara penjumlahan dengan adanya (#NUM!) di tengah - tengan Array yg kita set untuk dijumlahkan?

    Apakah harus menyelesaikan masalah di (#NUM!) dulu?

    ReplyDelete
    Replies
    1. Sebelumnya mohon maaf, karena respon sy sangat lambat sekali.
      Mungkin bapak/ibu/mas/mba/abang/kakak sudah dapat solusinya dari sumber lain.

      Salah satu solusi untuk masalah tersebut adalah dengan penjumlahan menggunakan fungi SUM dibantu fungsi IFERROR dalam bentuk rumus array

      Contoh: kolom1 berisi nama buah, kolom2 berisi jumlahnya, namun salah sel dalam kolom2 berisi nilai error. untuk mencari jumlah buah tertentu (misal pepaya) maka dapat menggunakan rumus berikut:

      =SUM((kolom1="pepaya")*IFERROR(kolom2,0))

      setelah mengetik rumus diatas tekan Ctrl + Shift + Enter
      sehingga penampakan rumus pada formula bar menjadi

      {=SUM((kolom1="pepaya")*IFERROR(kolom2,0))}

      Mohon maaf, dan semoga bermanfaat bagi siapapun yang memerlukan

      Delete
  4. Selamat sore, mau tanya jika kita ingin menjumlah dengan beberapa kriteria yang sejajar (bukan bertingkat). misal ada tabel dengan 2 kolom. kolom 1 berisi nama-nama buah (misal ada 50 item), kemudian kolom kedua adalah banyaknya buah dari masing-masing jenis buah yang tersedia. bagaimana jika saya ingin mengetahui jumlah total dari 4 jenis buah saja yaitu pepaya, mangga, pisang dan jambu. sehingga jika ada salak, kedondong, belimbing dll yang di luar 4 jenis buah tadi tidak ikut terjumlah. Tks sebelumnya atas pencerahan yang diberikan.

    ReplyDelete
    Replies
    1. Sebelumnya mohon maaf, karena respon sy sangat lambat sekali.
      Mungkin bapak/ibu/mas/mba/abang/kakak sudah dapat solusinya dari sumber lain.

      Salah satu solusi untuk masalah tersebut adalah dengan penjumlahan menggunakan fungi SUMPRODUCT

      Contoh: kolom1 berisi nama buah, kolom2 berisi jumlahnya. Untuk mencari jumlah beberapa jenis tertentu (misal: pepaya, pisang, mangga, jambu) maka dapat menggunakan rumus berikut:

      =SUMPRODUCT(((kolom1="pepaya")+(kolom1="pisang")+(kolom1="mangga")+(kolom1="jambu"))*kolom2)

      Terimakasih.

      Delete
    2. Saya mau tanya dong. Misalnya ada 4 kolom, terdiri dari buah, kota, jumlah Januari, jumlah februari.. saya mau kriterianya buah apel di kota jakarta yang dijumlahkan adalah jumlah januari & februari. apakah masih bisa menggunakan tanda + untuk kolom kriteria yang berbeda?

      Delete
  5. saya ingin nanya jika menggunakan sumif dan sum_rangenya saya hanya ingin 5 nilai tertinggi saya coba pake rumus diatas tidak bisa

    ReplyDelete
    Replies
    1. =SUMPRODUCT(LARGE((range=criteria)*(values),{1,2,3,4,5}))

      Delete
    2. Jika setting tanda koma sebagai pemisah desimal:

      =SUMPRODUCT(LARGE((range=criteria)*(values);{1\2\3\4\5}))

      Delete
  6. Terimakasih atas penjelasannya, sangat bermanfaat

    ReplyDelete
  7. Misal saya pakai rumus sumifs karena ada 3 syarat.Kolom 1 nama buah,kolom 2 warna,kolom 3 jumlah.Tpi jumlah ada yang #value..Cara oenggunaan sumifs dengan mengabaikan nilai error #value pada kolom 3 bgmn?

    ReplyDelete
  8. Ask
    saya ingin menjumlahkan data angka bercampur karakter huruf seperti 5 buah , 5 hari , psisi angka tersebut ada diatas dijumlahkan dengan angka yang ada dibawahnya, kira2 kaya apa rumusnya min ?

    ReplyDelete
  9. Bisa dijumlahkan langsung jika angka bercampur hurufnya dibuat menggunakan custom number format (jangan mengetikan huruf secara langsung di cell)
    Contoh format: 0" hari"

    ReplyDelete
  10. Sangat bermanfaat tetang SUMIF dengan mengambil data dari sheet lain.
    terima kasih

    ReplyDelete
  11. saya akan menggunakan sumif dengan workbook yang berbeda. akan tetapi hasil tersebut akan keluar "value" manakala workbook yang menjadi dasar pencarian tidak dibuka. bagaimana cara menyiasatinya ya???

    case : rumus yang saya pakai diworkbook (perbandingan analisa per tahun) --> =SUMIF('[Analisa Penjualan vs Komponen Biaya 2019.xlsx]Desember'!$C$7:$AM$194,C274,'[Analisa Penjualan vs Komponen Biaya 2019.xlsx]Desember'!$T$7:$T$187)

    dan hasilnya selalu value, jika file "Analisa penjualan vs komponen biaya 2019" tidak dibuka.
    apa yang harus saya tambahkan untuk rumus diatas???

    ReplyDelete
    Replies
    1. Problem yang sama, ada yang sudah menemukan solusinya kah?

      Delete
  12. Selamat Malam.
    Bagaimana jika saya gunakan rumus =SUMIF(A2:A10,"apel",C2:E10) namun hasil dari sortir, sehingga yang terjumlah tetap yang ditampilkan, bukan keseluruhan data (termasuk yang tidak ditampilkan/ hidden)
    Terima kasih atas responnya

    ReplyDelete

Terimakasih sudah berkunjung dan membaca blog ini. Silahkan berkomentar.