Penggunaan SUMIF, SUMIFS, DSUM, dan Formula Array untuk summary dengan kriteria tertentu

Seperti kita tahu fungsi SUM digunakan untuk menjumlahkan beberapa cells/angka tanpa syarat, pokoknya semua akan dijumlah tanpa memikirkan kondisinya.  Bagaimana jika kita akan menjumlahkan sekumpulan data dengan mempertimbangkan syarat/kriteria tertentu?

Jawabnya ada beberapa :

• Kita filter dulu sesuai syarat/kriteria yang akan kita pilih
• Kita pergunakan fungsi SUMIF jika syarat/kriteria hanya satu
• Kita pergunakan fungsi SUMIFS jika syarat/kriteria dua atau lebih
• Kita buat Formulai array SUM dengan syarat/kriteria
• Bisa juga kita pakai DSUM

Untuk butir 1 yaitu Filter tidak akan kita bahas disini

File latihan bisa didonlot disini (sumif sumifs 02.xlsx)

Misalnya kita memiliki laporan penjualan yang berisi tanggal transaksi dengan jumlah transaksi. disini kita akan mencoba menjumlahkan penjualan perbulan

 

Misalnya penjualan Januari, penjualan februari, penjualan jan s.d Februari, dst

Untuk itu kita akan menambahkan satu field baru yaitu dikolom C dng nama "Bulan" dengan formula

=MONTH(A2)

Kita ketik formula tadi dikolom C2 lalu copy sampai baris terakhir penjualan

 

1.  Menggunakan Sumif dan Sumifs

Syntax:

SUMIF(range, criteria, [sum_range])

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

Range = kolom yang berisi nilai yang akan kita bandingkan dengan kriteria

Criteria = text/angka berisi syarat/kondisi

Sum_Range = kolom berisi angka yang akan kita jumlah jika kondisi terpenuhi

Untuk Penjualan satu  bulan maka rumusnya

Penjualan Januari
=SUMIF($C$2:$C$546,"1",$B$2:$B$546)

Penjualan Februari
=SUMIF($C$2:$C$546,"2",$B$2:$B$546)

Penjualan Desember
=SUMIF($C$2:$C$546,"12",$B$2:$B$546)

Untuk Penjualan dua bulan atau lebih

Penjualan Jan s.d Feb
=SUMIF($C$2:$C$546,"<=2",$B$2:$B$546)

atau

=SUMIFS($B$2:$B$546,$C$2:$C$546,">=1",$C$2:$C$546,"<=2")

Penjualan Bulan Mei s.d Juli
=SUMIFS($B$2:$B$546,$C$2:$C$546,"<>1",$C$2:$C$546,"<>2",$C$2:$C$546,"<>3",$C$2:$C$546,"<>4",$C$2:$C$546,"<>8",$C$2:$C$546,"<>9",$C$2:$C$546,"<>10",$C$2:$C$546,"<>11",$C$2:$C$546,"<>12")
Rumus diatas mencari yang bukan penjualan bulan 1-4, dan bukan penjualan bulan 8-12

atau sederhananya

=SUMIFS($B$2:$B$546,$C$2:$C$546,">=5",$C$2:$C$546,"<=7")

Penjualan Beberapa bulan yang tidak bersambungan

Penjualan Bulan Mei dan Bulan Juli

=SUMIFS($B$2:$B$546,$C$2:$C$546,"<>1",$C$2:$C$546,"<>2",$C$2:$C$546,"<>3",$C$2:$C$546,"<>4",$C$2:$C$546,"<>6",$C$2:$C$546,"<>8",$C$2:$C$546,"<>9",$C$2:$C$546,"<>10",$C$2:$C$546,"<>11",$C$2:$C$546,"<>12")
rumus diatas akan mencari yang bukan penjualan bulan 1,2,3,4,6,8,9,10,11,12

atau simpelnya

=SUMIFS($B$2:$B$546,$C$2:$C$546,">=5",$C$2:$C$546,"<=7",$C$2:$C$546,"<>6")
rumus diatas akan mencari penjualan bulan 5 - 7 dan bukan bulan 6

Penjelasan :

• Rumus SUMIF dan SUMIFS sedikit berbeda pada posisi sum_Range, pada SUMIF ada dibelakang sementara pada SUMIFS ada didepan
• Maximal kriteria pada SUMIFS ada sebanyak 127 kriteria
• Angka bisa diiketik dengan atau tanpa tanda petik ganda, 12 dan "12" memberikan hasil yang sama
• Namun jika melibatkan text/operator maka harus diapit tanda petik ganda, ">5", "Apple" merupakan kriteria yang benar ,  bukan >5 atau Apple

2. Menggunakan DSUM

Dsum adalah fungsi database, yaitu menjumlahkan suatu tabel berdasarkan suatu suatu ktiteria tertentu. Yang membedakan dengan SUMIF/SUMIFS adalah kriterianya diketik pada suatu cell/range tertentu dan tabelnya kita pilih semua, untuk menentukan yang akan dijumlah maka kita ketik pada rumusnya nama field yang diinginkan

Syntax

DSUM(database,field,criteria)

Database = semua range table termasuk header

Field = nama dari field yang mau dijumlah, diapit kutip ganda, misalnya "Penjualan","Jumlah", dll

Criteria = range berisi header dan kriteria

Kita buat dulu kriteria dibaris 594 misalnya

 

Harap diingat :

• header harus sama dengan header Table
• Kalau sama baris maka operatornya adalah AND
• Kalau beda baris maka operatornya adalah OR

Untuk penjualan satu bulan

Penjualan Januari
=DSUM($A$1:$C$546,"Penjualan",$B$594:$B$595)

Penjualan Februari
=DSUM($A$1:$C$546,"Penjualan",$C$594:$C$595)

Penjelasan :

• Database = $A$1:$C$546,  jangan lupa header juga harus disertakan
• "Penjualan" merupakan nama field yang akan dijumlah
• Kriteria $B$594:$B$595 juga termasuk header
• 

Untuk penjualan lebih dari satu bulan

Penjualan Jan s.d Februari
=DSUM($A$1:$C$546,"Penjualan",$D$594:$E$595)

Penjualan Jan s.d Maret
=DSUM($A$1:$C$546,"Penjualan",$G$594:$H$595)

Penjualan Mei s.d Juli
=DSUM($A$1:$C$546,"Penjualan",$O$594:$P$595)

Penjelasan :

• Kriteria $D$594:$E$595, terdiri dari 2 kriteria dengan nama header yang sama, karena sama barisnya maka operator AND,  artinya bulan >=1 dan bulan <=2
• 

Penjualan Beberapa bulan yang tidak bersambungan

Penjualan Mei dan Juli

=DSUM($A$1:$C$546,"Penjualan",$Q$594:$R$596)

 

Penjelasan :

• Kriteria $Q$594:$R$596, terdiri dari 2 kriteria dengan nama header yang sama, namun berbeda baris, jadi menggunakan operator OR artinya bulan 5 atau bulan 7
• 

3.  Menggunakan Array Formula 

Sebagai pengenalan bisa dibaca

• Pengenalan Array Formula
• Mengextract angka dari cells yang berisi text dan angka

Penjualan Satu Bulan

Penjualan Januari
{=SUM(($C$2:$C$546=1)*$B$2:$B$546)}

Penjualan Februari
{=SUM(($C$2:$C$546=2)*$B$2:$B$546)}

Penjelasan :

• Perlu diketahui jika True di Excel berarti Nilai 1, sedangkan jika False akan menghasilkan 0
• Cara membacanya array formula penjualan bulan januari adalah adalah per-cell, jadi misalnya C2=1 (1 adalah bulan januari) , jika ya/true akan menghasilkan nilai 1 lalu dikalikan dengan B2 (penjualan) sedang jika false akan menghasilkan nol, lalu lanjut lagi C3 jika =1 maka menghasilkan nilai 1 lalu dikalikan dengan B3 sedang jika false akan menghasilkan nol, dst .... sampai dengan C546
• Lalu array2 diatas akan dilakukan kalkulasi dimana pada rumus ini adalah SUM
• Jadi hanya akan menghasilkan jumlah penjualan pada bulan 1
• Begitu juga cara membaca untuk rumus penjualan bulan maret

Penjualan 2 bulan atau lebih

Penjualan Jan s.d Februari

{=SUM(($C$2:$C$546>=1)*($C$2:$C$546<=2)*$B$2:$B$546)}

Penjualan Jan s.d Desember
{=SUM(($C$2:$C$546>=1)*($C$2:$C$546<=12)*$B$2:$B$546)}

 

Penjelasan :

• Perlu diketahui jika True di Excel berarti Nilai 1, sedangkan jika False akan menghasilkan 0
• Operator * (multiply) hanya akan bernilai 1  jika semua kriteria true
• Cara membacanya array formula penjualan bulan januari adalah adalah per-cell, jadi misalnya C2>=1 (1 adalah bulan januari) , jika ya/true akan menghasilkan nilai 1 lalu ditest lagi, C2<=2 (2 adalah bulan Februari) , jika ya/true akan menghasilkan nilai 1 dikalikan dengan B2 (penjualan) sedang jika false akan menghasilkan nol,  jika C2 adalah berada pada bulan jan s.d Feb maka hasilnya = (1) * (1) * (B2) =hasil penjualan, dst .... sampai dengan C546
• Jika C2 adalah bulan 5 misalnya maka hasilnya = (1) * (0) * (B3)=0
• Lalu array2 diatas akan dilakukan kalkulasi dimana pada rumus ini adalah SUM
• Jadi hanya akan menghasilkan jumlah penjualan pada bulan 1 s.d Bulan 2
• Begitu juga cara membaca untuk rumus penjualan bulan Jan s.d Des

Penjualan beberapa bulan yang tidak bersambung

Penjualan Mei dan Juli

{=SUM((($C$2:$C$546=5)+($C$2:$C$546=7))*$B$2:$B$546)}

 

Penjelasan :

• Perlu diketahui jika True di Excel berarti Nilai 1, sedangkan jika False akan menghasilkan 0
• Operator + akan bernilai 1 jika ada salah satu yang true
• Cara membacanya array formula penjualan bulan januari adalah adalah per-cell, jadi misalnya C2=5 (5 adalah bulan mei) , jika ya/true akan menghasilkan nilai 1 lalu ditest lagi, C2=7 (7 adalah bulan juli) , jika ya/true akan menghasilkan nilai 1, kedua hasil tadi akan ditambahkan dulu baru dikalikan dengan B2 (penjualan) , dst .... sampai dengan C546
• Jika C2 adalah berada pada bulan Mei maka hasilnya = ((1) + (0)) * (B2) =(1) * B2 =hasil penjualan
• Jika C2 adalah bulan Juni/6 misalnya maka hasilnya = ((0) + (0)) * (B3)=0
• Lalu array2 diatas akan dilakukan kalkulasi dimana pada rumus ini adalah SUM
• Jadi hanya akan menghasilkan jumlah penjualan pada bulan 5 dan Bulan 7