Penjumlahan Jika Lebih Besar dan Lebih Kecil

Penjumlahan bersyarat jika nilai lebih besar dan lebih kecil dapat diartikan juga sebagai penjumlahan bilangan yang terletak diantara nilai batas bawah dan nilai batas atas. Contoh praktisnya: Misalnya berapa sih jumlah total semua bilangan yang lebih besar dari 100, namun lebih kecil dari 200. Dalam hal ini nilai 100 sebagai batas bawah, dan nilai 200 sebagai batas atas, sehingga juga dapat dijabarkan sebagai penjumlahan semua bilangan yang ada diantara nilai 100 dan 200.

Rumus excel untuk melakukan penjumlahan bersyarat jenis ini tergolong mudah dan sederhana, cukup diperlukan satu fungsi saja yaitu fungsi SUMIFS. Sebagai alternatif, kita juga bisa membuat formula penjumlahan bersyarat jika lebih tinggi dan lebih rendah menggunakan fungsi SUMPRODUCT dan fungsi SUM. Penggunaan fungsi SUM harus dalam bentuk rumus array.

Contoh Rumus

=SUMIFS(B5:B14,B5:B14,">100",B5:B14,"<200")

Rumus tersebut dapat digunakan untuk menjumlahkan semua bilangan yang ada pada semua sel dalam range B5:B14 jika nilainya lebih besar dari 100 namun lebih kecil dari 200.

Rumus Umum:

=SUMIFS(rgJumlah,rgKriteria,kriteriaBawah,rgKriteria,kriteriaAtas)

Penjelasan:

• rgJumlah = rgKriteria, yaitu sebuah range berupa kolom dalam tabel, berisi bilangan yang akan dijumlahkan secara bersyarat jika lebih besar dan lebih kecil dari nilai tertentu. Dalam hal ini rgJumlah sama dengan rgKriteria karena kita menggunakan range yang sama, baik sebagai range nilai yang akan dijumlahkan maupun range sebagai tempat pengecekan kriteria lebih besar dan lebih kecil. Contoh: range B5:B14 
• kriteriaBawah = syarat nilai bilangan yang akan dijumlahkan yaitu harus lebih besar dari batas bawah. Contoh :  ">100"  artinya bilangan yang bisa dijumlahkan, nilainya harus lebih besar dari 100.
• kriteriaAtas = syarat nilai bilangan yang akan dijumlahkan yaitu harus lebih kecil atau kurang dari batas atas. Contoh: "<200" artinya bilangan yang bisa dijumlahkan, nilainya harus lebih kecil atau kurang dari 200.

Cara Kerja Rumus

Perhatikan contoh rumus:

=SUMIFS(B5:B14,B5:B14,">100",B5:B14,"<200")

Sebagaimana kita ketahui bahwa fungsi SUMIFS berguna untuk melakukan penjumlahan bersyarat dengan lebih dari satu kriteria. Ini artinya kita bisa menggunakan beberapa kriteria penjumlahan, seperti 2 kriteria, 3 kriteria, atau bahkan lebih banyak lagi.

Dalam hal contoh penjumlahan jika lebih besar dan lebih kecil, kita bisa menggunakan 2 kriteria:

• Kriteria1= kriteriaBawah, yaitu ">100"
• Kriteria2= kriteriaAtas, yaitu "<200"

Ini artiya bilangan yang dijumlahkan dalam range B5:B14 hanya bilangan yang nilainya lebih dari 100, tetapi kurang dari 200.

Perhatikan bilangan apa saja yang ada pada range B5:B14. Dari screenshot contoh di atas, kita dapat mengetahui bahwa bilangan yang memenuhi kriteria tersebut adalah 130, 150 dan 120. Sehingga formula akan memberikan hasil akhir 400, yaitu merupakan hasil penjumlahan 130 + 150 + 120.

Alternatif Rumus.

Selain menggunakan fungsi SUMIFS, sebagai alternatif kita juga bisa menggunakan fungsi lainnya untuk membuat rumus penjumlahan bersyarat jika lebih besar dan lebih kecil.

Dengan menggunakan contoh tabel yang sama, kita bisa menggunakan rumus berikut sebagai alternative.

Menggunakan fungsi SUMPRODUCT

=SUMPRODUCT((B5:B14>100)*(B5:B14<200)*B5:B14)

Menggunakan fungsi SUM dalam bentuk formula array

{=SUM((B5:B14>100)*(B5:B14<200)*B5:B14)}

Rumus array dapat diketahui dengan adanya tanda kurung kurawal {}, tanda tersebut jangan diketik secara langsung. Namun ketik rumus biasa (tanpa tanda kurung kurawal), setelah selesai mengetik rumus, kemudian tekan Ctrl + Shift + Enter, maka tanda kurung kurawal akan muncul pada bagian awal dan akhir rumus.

Demikian pembahasan singkat mengenai contoh rumus excel untuk menghitung jumlah bilangan yang lebih besar dan lebih kecil dari nilai tertentu dalam sebuah range data. Mudah-mudahan contoh sederhana ini bisa bermanfaat bagi pembaca.

Contoh Rumus Excel Lainnya

• Penjumlahan Jika Text dimulai Kata Tertentu
• Rumus Penjumlahan Setiap Baris ke-N
• Rumus Penjumlahan Setiap N Baris
• Rekapitulasi jumlah berdasarkan nama hari

Rumus Penjumlahan Setiap N Baris

Rumus penjumlahan setiap beberapa baris tertentu atau N baris sangat berguna jika kita memiliki sejumlah data dengan struktur urutan yang tetap. Misalnya menjumlahkan data setiap 5 hari sekali, 6 hari sekali atau 7 hari sekali dan bahkan seminggu sekali. Contoh rumus excel yang akan dibahas kali ini digunakan untuk menjumlahkan nilai setiap seminggu sekali dimana jumlah hari dalam seminggu diambil 5 hari kerja saja. Dengan kata lain kita akan menjumlahkan data setiap 5 baris. Untuk keperluan ini diperlukan gabungan fungsi SUM, OFFSET dan ROW.

Contoh Rumus:

=SUM(OFFSET(B5,(ROW()-6)*5,0,5,1))

Rumus Umum:

=SUM(OFFSET(selAwal,(ROW()-barisRumus1)*N,0,N,1))

Penjelasan:

• selAwal = sel pertama atau sel paling atas dari kolom data yang akan dijumlahkan nilainya, contoh sel B5
• barisRumus1 = Lokasi baris sel tempat mengetikan rumus yang dapat menghitung jumlah N baris pertama. Contoh: Sel tempat mengetikan rumus untuk menghitung jumlah nilai minggu ke-1 (5 baris pertama) adalah sel E6. Sel ini terletak pada baris ke-6. Sehingga kita bisa menentukan parameter barisRumus1 = 6.
• N = Nilai yang kita inginkan untuk menjumlahkan nilai setiap N baris.

Cara Kerja Rumus

Menghitung jumlah nilai pada 5 baris pertama (minggu ke-1):

Perhatikan contoh rumus pada sel E6 yang digunakan untuk menghitung nilai pada minggu pertama (5 baris pertama)

=SUM(OFFSET(B5,(ROW()-6)*5,0,5,1))

Fungsi ROW berguna untuk mengambil index baris sel dimana rumus diketik.  Karena rumus di ketik di sel E6 maka ROW() menghasilkan nilai 6. Sehingga (6-6)*5 = 0

Sehingga rumus OFFSET dapat dituliskan sebagai berikut:

=OFFSET(B5,0,0,5,1)

Hasil rumus OFFSET di atas akan mengacu ke range B5:B9

Mungkin pembaca masih bertanya-tanya mengapa contoh rumus OFFSET di atas menghasilkan range B5:B9

Rumus =OFFSET(B5,0,0,5,1) dapat dibaca sebagai berikut:  sebuah range yang jaraknya 0 baris dan 0 kolom dari sel B5 dimana range tersebut terdiri atas 5 baris dan 1 kolom. Maka range yang memenuhi kriteria tersebut adalah range B5:B9

Selanjutnya fungsi SUM bertugas menjumlahkan nilai pada range B5:B9

=SUM(B5:B9)

Dan hasilnya adalah 12

Menghitung Jumlah Nilai pada 5 Baris ke-2 (Minggu ke-2)

Selanjutnya mari kita lihat bagaimana hasilnya jika rumus =SUM(OFFSET(B5,(ROW()-6)*5,0,5,1)) diletakan di sel E7 untuk menghitung nilai pada 5 baris berikutnya (minggu ke-2). Rumusnya sama dengan penjumlahan minggu ke-1, namun akan menghasilkan jumlah nilai yang berbeda.

Kunci dari perbedaan ini adalah rumus ROW() yang akan menghasilkan nilai sesuai lokasi baris dimana rumus ditempatkan. Karena rumus ditempatkan di sel E7 maka rumus ROW() akan menghasilkan nilai 7 sehingga rumus dapat dijabarkan sebagai berikut:

=SUM(OFFSET(B5,(ROW()-6)*5,0,5,1))

=SUM(OFFSET(B5,(7-6)*5,0,5,1))

=SUM(OFFSET(B5,5,0,5,1))

=SUM(B10:B14)

=17

Perhatikan bagian rumus OFFSET(B5,5,0,5,1). Rumus ini dapat diterjemahkan sebagai berikut: sebuah range yang jaraknya 5 baris dan 0 kolom dari sel B5 dimana range tersebut terdiri atas 5 baris dan 1 kolom. Range yang memenuhi kriteria tersebut adalah range B10:B14 yang merupakan 5 baris ke 2 dalam range data yang ingin kita hitung jumlah nilainya.

Demikian pembahasan singkat mengenai contoh rumus untuk menghitung jumlah nilai setiap beberapa baris di excel. Semoga penjelasannya mudah difahami dan bisa bermanfaat.

Contoh Rumus Excel Lainnya

• Rekapitulasi jumlah berdasarkan nama hari
• Rekapitulasi Jumlah Mingguan
• Rumus Penjumlahan Berdasarkan Bulan

Rekapitulasi Jumlah Berdasarkan Hari

Rumus excel untuk rekapitulasi jumlah nilai pada hari tertentu dapat dibuat menggunakan fungsi SUMPRODUCT dan dibantu dengan fungsi WEEKDAY. Sebagai alternative, fungsi SUMPRODUDUCT juga dapat digantikan oleh fungsi SUM, namum rumus harus dituliskan dalam bentuk formula array.

Contoh:

=SUMPRODUCT((WEEKDAY($A$5:$A$14)=D5)*$B$5:$B$14)

Untuk mempersingkat penulisan dan memudahkan pembacaan rumus, kita hilangkan tanda dolarnya:

=SUMPRODUCT((WEEKDAY(A5:A14)=D5)*B5:B14)

 

Rumus Umum:

=SUMPRODUCT((WEEKDAY(rgTanggal)=hari)*rgJumlah)

Penjelasan:

• rgTanggal = range berupa kolom dalam tabel berisi data ber-type tanggal. Contoh: range A5:A14
• rgJumlah = range berupa kolom berisi data jumlah per masing-masing tanggal. Contoh: range B5:B14
• hari = sel berisi data berupa bilangan 1 s.d 7 yang diberi format number "[$-421]dddd" sehingga secara nampak secara visual sebagai nama hari. Angka 1 mewakili hari Minggu, Angka 2 mewakili hari Senin, demikian seterusnya sampai angka 7 mewakili hari Sabtu. Contoh : Sel D5 memimiliki konten angka 1 yang diformat "[$-421]dddd" sehingga terbaca sebagai hari Minggu.

Cara Kerja Rumus

Perhatikan contoh rumus pada sel E5 pada gambar di atas, untuk rekapitulasi jumlah berdasarkan nama hari 

=SUMPRODUCT((WEEKDAY(A5:A14)=D5)*B5:B14)

Fungsi WEEKDAY berguna unguk mengkonversi data tanggal yang ada pada range A5:A14 menjadi bilangan 1 s/d 7 yang mewakili hari Minggu s/d hari Sabtu. Kemudian hasil fungsi WEEKDAY terebut dicompare dengan nilai yang ada pada sel D5. Sel D5 berisi angka 1 dengan number format "[$-421]dddd" sehingga sel menampakan text “Minggu”.

Hasil komparasi atau perbandingan antara nilai WEEKDAY atas data tanggal pada range A5:A14 dengan bilangan pada sel D5 akan menghasilkan nilai boolean TRUE atau FALSE. TRUE menunjukan nilai komparasi yang sama, sedangkan FALSE, berarti sebaliknya yaitu tidak sama.

Pada lembar kerja excel, silahkan diseleksi bagian rumus yang di-highlight

=SUMPRODUCT((WEEKDAY(A5:A14)=D5)*B5:B14)

Kemudian tekan F9, maka rumus akan berubah menjadi seperti berikut ini.

=SUMPRODUCT(({FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE})*B5:B14)

Selanjutnya silahkan diseleksi kembali bagian rumus B5:B14, kemudian tekan F9, sehingga berubah menjadi array bilangan yang adalam dalam range B5:B14

=SUMPRODUCT(({FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE})*{2;1;2;3;4;2;3;4;4;4})

Nilai boolean TRUE dan FALSE kemudian diperkalikan dengan data jumlah yang ada pada range B5:B14.

Sebagaimana kita ketahui, pada proses perkalian, TRUE akan diperlakukan sebagai bilangan 1, sedangkan FALSE diperlakukan sebagain bilangan 0. Dengan demikian rumus dapat dituliskan menjadi seperti di bawah ini: (diperoleh dengan cara seleksi bagian rumus yang merupakan semua parameter fungsi SUMPRODUCT, kemudian tekan F9)

=SUMPRODUCT({0;0;0;3;0;0;0;0;4;0})

Terakhir, fungsi SUMPRODUCT menyelesaikan tugasnya dengan menjumlahkan semua bilangan yang ada dalam array. Hasilnya adalah 7.

Alternative Rumus Rekapitulasi Jumlah Berdasarkan Hari

Selain menggunakan fungsi SUMPRODUCT seperti yang sudah dijelaskan di atas,  Kita juga bisa mengganti fungsi SUMPRODUCT dengan fungsi SUM yang dibuat dalam bentuk rumus array. Rumus array dibuat dengan cara menekan Ctrl + Shift + Enter begitu kita selesai mengetikan rumus.

Berikut contoh rumusnya:

{=SUM((WEEKDAY(A5:A14)=D5)*B5:B14)}

Demikian pebahasan singkat mengenai contoh rumus excel untuk rekapitulasi data berdasarkan nama hari.

Contoh Rumus Excel Lainnya

• Rekapitulasi Jumlah Mingguan
• Rumus Penjumlahan Berdasarkan Bulan
• Rumus Penjumlahan Berdasarkan Grup

Rekapitulasi Jumlah Mingguan

Rumus excel untuk rekapitulasi jumlah mingguan atau penjumlahan berdasarkan data mingguan dapat dilakukan menggunakan bantuan fungsi SUMIFS. Fungsi ini bekerja dengan cara menjumlahkan data pada tanggal ditentukan sampai dengan kurang dari 7 hari kedepan. Sebagai alternative, kita juga bisa menggunakan fungsi SUMPRODUCT untuk membuat summary data mingguan. Selain itu, fungsi SUM juga dapat digunakan untuk keperluan ini, namun harus dalam bentuk formula array.

Contoh:

=SUMIFS(B5:B14,A5:A14,">="&D5,A5:A14,"<"&D5+7)

Rumus Umum:

=SUMIFS(rgJumlah,rgTanggal,">="&mingguan,rgTanggal,"<="& mingguan+7)

Penjelasan:

rgTanggal = range berupa kolom dalam tabel berisi data ber-type tanggal. Contoh: range A5:A14

rgJumlah = range berupa kolom berisi data jumlah per masing-masing tanggal. Contoh: range B5:B14

mingguan = sel berisi data bertype tanggal, disusun dalam rentang waktu seminggu sekali. Contoh: sel D5, rekapitulasi jumlah mingguan ditentukan setiap hari Selasa sampai dengan hari Senin berikutnya.

Cara Kerja Rumus

Perhatikan contoh rumus untuk rekapitulasi jumlah mingguan pada sel E5 dalam gambar di atas

=SUMIFS(B5:B14,A5:A14,">="&D5,A5:A14,"<"&D5+7)

Dapat kita lihat dari rumus di atas. Ternyata kita hanya memerlukan satu fungsi SUMIFS dengan 2 kondisi untuk membuat rumus rekapitulasi mingguan.

• Kondisi 1 = Tanggal >= Tanggal pertama yang ditentukan dalam mingguan.
• Kondisi 2 = Tanggal < tanggal pertama yang ditentukan dalam minggu berikutnya.

Fungsi SUMIFS dengan 2 kondisi dapat dituliskan dengan syntax berikut:

=SUMIFS(sum_range,criteria_range1,criteria1, criteria_range2,criteria2)

Sum_range = rgJumlah ⇒ adalah range yang akan dijumlahkan nilainya yaitu B5:B14

Criteria_range1 = criteria_range2 = rgTanggal ⇒ adalah range yang dijadikan sebagai dasar penjumlahan jika kondisi atau kriterianya terpenuhi, yaitu range A5:B14

Criteria1 ⇒ adalah kondisi 1 yang harus terpenuhi, yaitu tanggal harus ">=mingguan" , dituliskan “>=”&D5 karena tanggal mingguan terletak di sel D5

Criteria2 ⇒ adalah kondisi 2 yang harus terpenuhi, yaitu tanggal harus "<(mingguan+7)", dituliskan "<"&D5+7 karena tanggal mingguan terladak pada sel D5

Dengan demikian rumus tersebut di atas akan menghitung berapa jumlah data selama 7 hari dimulai dari tanggal ditentukan. Penjumlahan data selama 7 hari dapat dikatakan sebagai penjumlahan arau rekapitulasi mingguan.

Alternative Rumus Rekapitulasi Mingguan

Selain menggunakan fungsi SUMIFS seperti yang sudah dijelaskan di atas.  Kita juga bisa menggunakan rumus alternative lainnya.

Rekapitulasi mingguan menggunakan fungsi SUMPRODUCT

=SUMPRODUCT((A5:A14>=D5)*(A5:A14<D5+7)*B5:B14)

Rekapitulasi mingguan menggunakan fungsi SUM dalam bentuk rumus array.

{=SUM((A5:A14>=D5)*(A5:A14<D5+7)*B5:B14)}

Keterangan: cara kerja dan cara mengetik parameter rumus SUMPRODUCT dan rumus array SUM hampir sama. Hanya saja, pada saat selesai mengetik rumus SUM, kita harus menekan Ctrl + Shift + Enter untuk menjadikannya sebagai rumus array.

Contoh Rumus Excel Lainnya

• Rumus Penjumlahan Berdasarkan Bulan
• Rumus Penjumlahan Berdasarkan Grup
• Menjumlahkan Nilai Terendah Dengan Kriteria

Rumus Penjumlahan Berdasarkan Grup Bulan

Menjumlahkan nilai berdasarkan grup bulan yang akan dibahas dalam kesempatan ini tidak mempertimbangkan tahun. Artinya data pada bulan yang sama namun tahun berbeda akan turut terjumlah. Oleh karena itu cara ini disarankan hanya digunakan terhadap satu tahun periode data saja. Adapun fungsi yang digunakan adalah SUMPRODUCT dan MONTH. Sebagai alternative, juga boleh menggunakan kombinasi SUM dan MONTH dalam bentuk formula array.

Contoh:

 =SUMPRODUCT((MONTH(A5:A14)=MONTH(D5))*B5:B14)

Rumus Umum:

=SUMPRODUCT((MONTH(rgTanggal)=MONTH(bulan))*rgJumlah)

Penjelasan:

rgTanggal = range berupa sebuah kolom dalam tabel berisi data ber-type tanggal (date)

bulan = sebuah sel berisi type data tanggal dengan format “mmmm” nampak nama bulannya saja. Contoh: tgl 1/1/2018 dengan format "mmmm" akan nampak sebagai bulan "January"

rgJumlah = range berupa sebuah kolom yang sejajar dengan rgTangal berisi nilai – niai yang akan dijumlahkan

Cara Kerja Rumus

Perhatikan contoh rumus sesuai gambar pada sel E5 sesuai gambar di atas:

=SUMPRODUCT((MONTH(A5:A14)=MONTH(D5))*B5:B14)

Mari kita pecah rumus tersebut menjadi beberapa langkah:

Langkah 1:  

=(MONTH(A5:A14)=MONTH(D5) )

Expresi ini berguna untuk mengecek nilai bulan pada masing-masing sel di dalam range A5:A14 apakah sama atau tidak dengan nilai bulan pada data tanggal di sel D5. Proses ini menghasilkan array berisi  data boolean TRUE dan FALSE.  Pada formula bar silahkan diseleksi bagian rumus (MONTH(A5:A14)=MONTH(D5)), kemudian tekan F9, maka kita akan memperoleh sebuah array berisi TRUE dan FALSE

{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Langkah 2:  

=(MONTH(A5:A14)=MONTH(D5) * B5:B14

= hasilLangkah1 * B5:B14

Langkah ke-2 ini akan mengalikan hasil dari langkah ke-1 dengan range B5:B14

Nilai dalam range B5:B14 jika dituliskan dalam bentuk array adalah:

{2;1;2;3;4;2;3;4;4;4}

Sehingga rumus =hasilLangkah1*B5:B14 dapat dijabarkan sebagai berikut:

={TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*{2;1;2;3;4;2;3;4;4;4}

Sebagai mana  kita ketahui TRUE sepadan dengan nilai 1, sedangkan FALSE sepadan dengan nol. Sehingga perkalian tersebut akan menghasilkan array berikut:

={2;1;0;3;0;0;0;0;0;0}

Hasil dari langkah 2 ini jika berdiri sendiri hanya akan menghasilkan nilai 2 yaitu elemen pertama dalam array. Untuk menjumlahkan semua elemen dalam array, maka diperlukan bantuan fungsi SUMPRODUCT

Langkah 3:  

=SUMPRODUCT((MONTH(A5:A14)=MONTH(D5))*B5:B14)

=SUMPRODUCT(hasilLangkah2)

Fungsi SUMPRODUCT berguna untuk menjumlahkan semua elemen array hasil langkah 2, sehingga rumus dapat dijabarkan sebagai berikut:

=SUMPRODUCT({2;1;0;3;0;0;0;0;0;0})

Dan hasil akhirnya adalah 6. Hasil ini merupakan penjumlahan semua nilai pada bulan Januari.

Catatan: 

Pada penjelasan di atas semua alamat range menggunakan referensi relative dengan tujuan supaya lebih mudah membacanya. Agar referensi rgTanggal dan rgJumlah tidak bergeser pada saat rumus di copy paste ke sel di bawahnya, maka disarankan menggunakan referensi absolute. Oleh karenanya contoh rumus di atas dapat diedit kembali menjadi sebagai berikut:

=SUMPRODUCT((MONTH($A$5:$A$14)=MONTH(D5))*$B$5:$B$14)

Alternative Rumus Penjumlahan Berdasarkan Grup Bulan.

Selain menggunakan SUMPRODUCT, kita juga bisa memanfaatkan fungsi SUM dalam bentuk formula array.

Caranya:

Ketik rumus berikut di sel E5

=SUM((MONTH(A5:A14)=MONTH(D5))*B5:B14)

Begitu selesai mengetik rumus, lanjutkan dengan menekan Ctrl + Shift + Enter untuk menjadikannya sebagai formula array, maka pada formula Bar kita akan melihat rumus tadi berada dalam tanda kurung kurawal {}.

{=SUM((MONTH(A5:A14)=MONTH(D5))*B5:B14)}

Cara kerja rumus array ini hampir sama dengan rumus SUMPRODUCT.

Demikian pembahasan singkat mengenai contoh rumus excel untuk rekapitulasi penjumlahan nilai berdasarkan grup bulan.

Contoh Rumus Excel Lainnya

• Rumus Penjumlahan Berdasarkan Grup
• Menjumlahkan Nilai Terendah Dengan Kriteria
• Menjumlahkan Nilai Tertinggi Dengan Kriteria

Menjumlahkan Nilai Terendah Dengan Kriteria

Dalam pembahasan beberapa waktu yang lalu sudah dibahas contoh rumus excel untuk menjumlahkan beberapa nilai terendah atau terkecil. Dalam contoh yang lalu tersebut tidak ditentukan kriteria atau syarat apapun dalam proses penjumlahan. Nah, sedangkan kali ini kali ini kita akan mengupas contoh rumus excel untuk menjumlahkan nilai terendah yang memenuhi kriteria tertentu. Adapun fungsi yang dapat digunakan adalah kombinasi SUM, SMALL dan IF dalam bentuk formula array.

Contoh

Rumus Umum:

{=SUM(SMALL(IF(rangeKriteria=kriteria,rangeJumlah),{1,2,N}))}

Catatan: tanda kurung kurawal {} pada bagian awal dan akhir rumus merupakan tanda rumus array jangan diketik secara langsung, melainkan dengan cara menekan Ctrl + Shift + Enter setelah mengetik rumus.

Penjelasan:

• rangeKriteria = range berupa kolom dalam kerja excel yang berisi data kriteria yang dipertimbangkan dalam proses penjumlahan. Contoh : range $B$5:$B$14, berupa kolom dalam tabel berisi konten berupa  text yang merupakan kriteria warna.
• kriteria = jenis kriteria yang ditentukan untuk proses penjumlahan. Contoh : kriteria warna merah (sel D5)
• rangeJumlah = range berupa kolom dalam lembar kerja excel yang berisi data nilai yang ingin kita jumlahkan beberapa nilai terkecilnya dengan syarat memenuhi kriteria yang ditentukan. Contoh : range $C$4$C$13
• N = Bilangan dalam tanda kurung kurawal sejumlah 1 bilangan atau lebih menunjukan nilai tertinggi ke berapa saja yang ingin dijumlahkan, misalnya:

Menjumlahkan 2 nilai terkecil dengan kriteria:

{=SUM(SMALL(IF(rangeKriteria=kriteria,rangeJumlah),{1,2}))}

Menjumlahkan 3 nilai terkecil dengan kriteria:

{=SUM(SMALL(IF(rangeKriteria=kriteria,rangeJumlah),{1,2,3}))}

Menjumlahkan 4 nilai terkecil sesuai kriteria:

{=SUM(SMALL(IF(rangeKriteria=kriteria,rangeJumlah),{1,2,3,4}))}

Menjumlahkan nilai terkecil ke-1 dan ke-3 sesuai kriteria:

{=SUM(SMALL(IF(rangeKriteria=kriteria,rangeJumlah),{1,3}))}

Cara Kerja Rumus

Mari kita lihat kembali rumus umum dan bandingkan dengan contoh rumus dalam gambar di atas:

{=SUM(SMALL(IF(rangeKriteria=kriteria,rangeJumlah),{1,2,3}))}

{=SUM(SMALL(IF($A$5:$A$14=D5,$B$5:$B$14),{1,2,3}))}

Langkah 1: 

IF(rangeKriteria=kriteria,rangeJumlah) ⇒ IF($A$5:$A$14=D5,$B$5:$B$14)

Merupakan sebuah ekspresi untuk mengecek konten dalam sel-sel yang ada di dalam range A5:A14 apakah sama dengan nilai di D5 atau tidak. Jika sesuai maka akan dihasilkan nilai pada sel sejajar dalam range B5:B14. Jika tidak maka akan menghasilkan nilai FALSE. Silahkan seleksi bagian rumus IF($A$5:$A$14=D5,$B$5:$B$14), kemudian tekan F9 , maka akan didapatkan sebuah array berikut:

{2;3;FALSE;4;FALSE;FALSE;5;FALSE;FALSE;6}

Langkah 2:

=SMALL(IF(rangeKriteria=kriteria,rangeJumlah),{1,2,3}) ⇒ 

=SMALL(hasilLangkah1,{1,2,3}) ⇒  

=SMALL({2;3;FALSE;4;FALSE;FALSE;5;FALSE;FALSE;6},{1,2,3})

Fungsi SMALL berguna untuk mendapatkan nilai terendah  diantara bebeapa bilangan. fungsi ini  akan mengabaikan nilai FALSE, dan mengembalikan nilai lainnya (terendah ke-1, terendah ke-2 dan terendah ke-3)

Hasilnya adalah: {2,3,4}

Langkah 3:

=SUM(SMALL(IF(rangeKriteria=kriteria,rangeJumlah),{1,2,3}))} ⇒ 

=SUM(hasilLangkah2)

=SUM({2,3,4})

Fungsi SUM berguna untuk menjumlahkan hasil dari langkah sebelumnya. Dan hasilnya adalah 2 + 3 + 4 = 9

..

Demikian pembasan singkat mengenai contoh rumus excel untuk menjumlahkan nilai terkecil dengan kriteria. Semoga penjelasan yang singkat ini mudah difahami dan semoga bermanfaat.

Terimakasih.

Lihat juta contoh rumus excel lainnya:

• Menjumlahkan Nilai Tertinggi Dengan Kriteria
• Menjumlahkan Nilai Tertinggi di Excel
• Mencari Nilai Tertinggi dan Terendah

Menjumlahkan Nilai Tertinggi Dengan Kriteria

Dalam pembahasan beberapa waktu yang lalu sudah dibahas contoh rumus excel untuk menjumlahkan beberapa nilai tertinggi atau top value menggunakan kombinasi fungsi SUMPRODUCT dan LARGE serta alternative lainnya menggunakan  rumus array SUM dan LARGE. Dalam contoh yang lalu tersebut tidak ditentukan kriteria atau syarat apapun dalam proses penjumlahan. Nah, sedangkan kali ini kita akan mengupas contoh rumus excel untuk menjumlahkan nilai tertinggi yang memenuhi kriteria tertentu. Adapun fungsi yang dapat digunakan masih sama, yaitu kombinasi SUMPRODUCT dan LARGE, dan kombinasi SUM dan LARGE dalam bentuk formula array.

Contoh:

Rumus Umum:

=SUMPRODUCT(LARGE((rangeKriteria=kriteria)*rangeJumlah,{1,2,N}))

Penjelasan:

• rangeKriteria = range berupa kolom dalam kerja excel yang berisi data kriteria yang dipertimbangkan dalam proses penjumlahan. Contoh : range $B$5:$B$14, berupa kolom dalam tabel berisi konten berupa  text yang merupakan kriteria warna.
• kriteria = jenis kriteria yang ditentukan untuk proses penjumlahan. Contoh : kriteria warna merah (sel D5)
• rangeJumlah = range berupa kolom dalam lembar kerja excel yang berisi data nilai yang ingin kita jumlahkan beberapa nilai tertingginya dengan syarat memenuhi kriteria yang ditentukan. Contoh : range $C$4$C$13
• N = Bilangan dalam tanda kurung kurawal sejumlah 1 bilangan atau lebih menunjukan nilai tertinggi ke berapa saja yang ingin dijumlahkan, 

Misalnya:

Menjumlahkan 2 nilai tertinggi sesuai kriteria:

=SUMPRODUCT(LARGE((rangeKriteria=kriteria)*rangeJumlah,{1,2}))

Menjumlahkan 3 nilai tertinggi sesuai kriteria:

=SUMPRODUCT(LARGE((rangeKriteria=kriteria)*rangeJumlah,{1,2,3}))

Menjumlahkan 4 nilai tertinggi sesuai kriteria:

=SUMPRODUCT(LARGE((rangeKriteria=kriteria)*rangeJumlah,{1,2,3,4}))

Menjumlahkan nilai tertinggi ke-1 tertinggi dan ke-3 sesuai kriteria:

=SUMPRODUCT(LARGE((rangeKriteria=kriteria)*rangeJumlah,{1,3}))

Cara Kerja Rumus

Mari kita lihat kembali rumus umum dan bandingkan dengan contoh rumus dalam gambar di atas:

=SUMPRODUCT(LARGE((rangeKriteria=kriteria)*rangeJumlah,{1,2,N}))

=SUMPRODUCT(LARGE(($A$5:$A$14=D5)*$B$5:$B$14,{1,2,3}))

Mari kita lihat langkah-langkahnya:

Langkah 1:
(rangeKriteria=kriteria) ⇒ (($A$5:$A$14=D5)

Merupakan sebuah ekspresi untuk mengecek konten dalam sel-sel yang ada di dalam range A5:A15 apakah sama dengan nilai di D5 atau tidak. Proses ini menghasilan nilai boolean TRUE  jika benar dan FALSE jika salah. Dalam lembar kerja excel, silahkan seleksi bagian rumus ($B$4:$B$14=D5), kemudian tekan F9 , maka akan didapatkan sebuah array berikut:

{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

Langkah 2: 
rangeJumlah  ⇒ $B$5:$B$14

silahkan seleksi bagian rumus $B$5:$B$14, kemudian tekan F9 untuk melihat nilai di dalamnya, maka hasilnya adalah sebagai berikut:

{2;3;1;4;2;3;5;4;5;6}

Langkah 3: 
=(kriteria)*rangeJumlah ⇒ hasilLangkah1 * hasilLangkah2

Jika bagian rumus tersebut kita ganti dengan elemen-elemen di dalamnya maka bisa dituliskan sebagai berikut:

={TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}*{2;3;1;4;2;3;5;4;5;6}

Atau bisa dituliskan seperti di bawah ini:

={TRUE*2;TRUE*3;FALSE*1;TRUE*4;FALSE*2;FALSE*3;TRUE*5;FALSE*4; FALSE*5;TRUE*6}

Dalam proses perkalian, TRUE akan terbaca sebagai 1, sebaliknya FALSE akan terbaca sebagai 0. Maka hasil dari proses perkalian antar array di atas adalah:

={2;3;0;4;0;0;5;0;0;6}

Langkah 4: 

LARGE((rangeKriteria=kriteria)*rangeJumlah,{1,2,N}) ⇒ LARGE(hasilLangkah3,{1,2,3}) ⇒ LARGE({2;3;0;4;0;0;5;0;0;6},{1,2,3})

Fungsi LARGE digunakan untuk mendapatkan nilai tertinggi ke-1, ke-2 dan ke-3 di antara elemen array ({2;3;0;4;0;0;5;0;0;6}, sehingga hasilnya adalah sebagai berikut:

{6,5,4}

Langkah 5: 

=SUMPRODUCT(LARGE((rangeKriteria=kriteria)*rangeJumlah,{1,2,N})) ⇒ =SUMPRODUCT(hasilLangkah4) ⇒ =SUMPRODUCT({6,5,4})

Fungsi SUMPRODUCT berperan untuk menjumlahkan nilai elemen-eleman dalam array {6,5,4}. Dan hasilnya adalah 6 + 5 + 4 = 15

Alternative contoh rumus excel lainnya untuk menghitung nilai tertinggi dengan kriteria.

Selain menggunakan kombinasi fungsi SUMPRODUCT dan LARGE, pejumlahan nilai tertinggi di excel juga bisa dilakukan menggunakan kombinasi fungsi SUM dan LARGE. Namun penerapannya harus dalam bentuk formula array.

Caranya: 

Ketik rumus berikut di sel E5

=SUM(LARGE(($A$5:$A$14=D5)*$B$5:$B$14,{1,2,3}))

Setelah mengetik rumus di atas, jangan hanya menekan enter seperti biasa, akan tetapi tekan secara bersamaan tombol Ctrl + Shift + Enter. Maka di formula bar, kita akan melihat rumus berada dalam tanda kurung kurawal yang merupakan ciri sebuah rumus array.

{=SUM(LARGE(($A$5:$A$14=D5)*$B$5:$B$14,{1,2,3}))}

Hasil rumus akan persis sama dengan hasil dari contoh pertama menggunakan fungsi SUMPRODUCT.

----

Sampai di sini pembahasan singkat mengenai contoh rumus excel untuk menjumlahkan nilai tetinggi dengan kriteria, dan semoga bermanfaat.

Contoh Rumus Excel Lainnya

• Menjumlahkan Nilai Tertinggi Di Excel
• Menjumlahkan Nilai Terendah di Excel
• Rumus Penjumlahan Berdasarkan Kriteria Warna
• Rumus SUM 3 Dimensi Untuk Penjumlahan Multi Sheet

Menjumlahkan Nilai Terendah di Excel

Menjumlahkan beberapa nilai terendah atau terkecil di Excel dapat dilakukan dengan mudah menggunakan bantuan rumus. Adapun fungsi yang dapat digunakan untuk keperluan ini adalah gabungan fungsi SUMPRODUCT dan fungsi SMALL. Selain itu, penjumlahan nilai terendah juga bisa dilakukan menggunakan kombinasi fungsi SUM dan SMALL 

Contoh:

Rumus Umum:

=SUMPRODUCT(SMALL(rangeJumlah,{1,2,N}))

Penjelasan:

rangeJumlah = range dalam lembar kerja excel yang berisi data bilangan yang ingin kita jumlahkan beberapa nilai terkecil nya.

N = Bilangan dalam tanda kurung kurawal sejumlah 1 bilangan atau lebih menunjukan nilai terendah ke berapa saja yang ingin dijumlahkan, misalnya:

Menjumlahkan 2 nilai terendah:

=SUMPRODUCT(SMALL(rangeJumlah,{1,2}))

Menjumlahkan 3 nilai terendah;

=SUMPRODUCT(SMALL(rangeJumlah,{1,2,3}))

Menjumlahkan 4 nilai terendah;

=SUMPRODUCT(SMALL(rangeJumlah,{1,2,3,4}))

Menjumlahkan nilai terendah ke-1 dan ke-4

=SUMPRODUCT(SMALL(rangeJumlah,{1,4}))

Sesuai gambar contoh, sel aktif yaitu cell E4 memiliki formula sebagai berikut: =SUMPRODUCT(SMALL(B4:B13,{1,2}))

Rumus tersebut dapat diterjemahkan sebagai berikut: menjumlahkan nilai terkecil ke-1 dan terkecil ke-2 dari kumpulan nilai yang ada di dalam range B4:B13. Dengan kata lain, dapat juga diartikan sebagai penjumlahan 2 bilangan terkecil di antara beberapa bilangan yang ada dalam range B4:B13

Cara Kerja Rumus

Fungsi SMALL berperan untuk mendapatkan nilai tekecil ke-N dari bilangan-bilangan yang ada dalam rangeJumlah (range B4:B13)

= SMALL(B4:B13,1) ⇒ mendapatkan nilai terkecil ke-1 dari range B4:B13

= SMALL(B4:B13,2) ⇒ mendapatkan nilai terkecil ke-2 dari range B4:B13

Karena kita menginginkan beberapa tingkat nilai nilai terendah , maka parameter N perlu diletakan di dalam tanda kurang kurawal

=SMALL(B4:B13,{1,2}) ⇒ Mendapatkan nilai terkecil ke-1 dan ke-2 dari range B4:B13

Rumus ini jika diletakan tersendiri maka akan menghasilkan nilai N pertama dalam array. Dalam hal ini adalah nilai terendah ke-1 karena angka 1 merupakan elemen pertama dalam array {1,2}. Namun jika rumus tersebut dikombinasikan dengan fungsi SUMPRODUCT, maka kedua nilai terendah tesebut dapat dikalkulasi dan dijumlahkan.

Fungsi SUMPRODUCT berperan untuk menjumlahkan semua nilai yang dihasilkan oleh fungsi SMALL

=SUMPRODUCT(SMALL(B4:B13,{1,2})) ⇒ Fungsi SUMPRODUCT bekerja untuk menjumlahkan bilangan terendah ke-1 dan terendah ke-2 dalam range B4:B13 yang dihasilkan oleh fungsi SMALL.

Hasilnya adalah 3, yaitu merupakan hasil penjumlahan bilangan 1 (nilai terkecil ke-1) dan bilangan 2 (nilai terkecil ke-2)

Silahkan dicoba dan perhatikan hasilnya, jika kita merubah elemen-elemen array, misalnya:

=SUMPRODUCT(SMALL(B4:B13,{1,2}))

=SUMPRODUCT(SMALL(B4:B13,{1,2,3}))

=SUMPRODUCT(SMALL(B4:B13,{1,4}))

Alternative Rumus Lainnya.

Sebagai alternative, kita juga bisa mengganti fungsi SUMPRODUCT dengan fungsi SUM dalam bentuk formula array untuk menjumlahkan beberapa nilai terendah.

Caranya:

Ketik contoh rumus berikut pada sel E4

=SUM(SMALL(B4:B13,{1,2}))

Begitu selesai diketik, kemudian tekan Ctrl + Shift + Enter.

Maka di formula bar, kita bisa melihat bahwa rumus tersebut berada di dalam tanda kurung kurawal. Tanda inilah yang menunjukan bahwa sebuah rumus merupakan rumus array.

{=SUM(SMALL(B4:B13,{1,2}))}

Hasilnya = 3

Catatan: Terlepas dari penjelasan di atas, ternyata kombinasi fungsi SUM dan SMALL  juga dapat bekerja dengan baik tanpa perlu menggunakan bentuk rumus array.

Demikian pembahasan super singkat mengenai contoh rumus untuk menjumlahkan beberapa nilai terendah atau terkecil di excel.  Semoga bermanfaat

Contoh Rumus Excel Lainnya

• Menjumlahkan Nilai Tertinggi Di Excel
• Mencari Nilai Tertinggi dan Terendah
• Menjumlahkan Berdasarkan Warna di Excel

Menjumlahkan Nilai Tertinggi Di Excel

Menjumlahkan beberapa nilai tertinggi atau top value di Excel sangat mudah dilakukan dengan cara menggunakan rumus. Adapun fungsi yang dapat digunakan untuk keperluan ini adalah gabungan fungsi SUMPRODUCT dan fungsi LARGE. Selain itu, penjumlahan nilai tertinggi juga bisa dilakukan menggunakan kombinasi fungsi SUM dan LARGE.

Contoh:

Rumus Umum:

=SUMPRODUCT(LARGE(rangeJumlah,{1,2,N}))

Penjelasan:

• rangeJumlah = range dalam lembar kerja excel yang berisi data bilangan yang ingin kita jumlahkan beberapa nilai tertinggi nya.
• N = Bilangan dalam tanda kurung kurawal sejumlah 1 bilangan atau lebih menunjukan nilai tertinggi ke berapa saja yang ingin dijumlahkan, 

Cara menterjemahkan formula, misalnya:

Menjumlahkan 2 nilai tertinggi:

=SUMPRODUCT(LARGE(rangeJumlah,{1,2}))

Menjumlahkan 3 nilai tertinggi;

=SUMPRODUCT(LARGE(rangeJumlah,{1,2,3}))

Menjumlahkan 4 nilai tertinggi;

=SUMPRODUCT(LARGE(rangeJumlah,{1,2,3,4}))

Menjumlahkan nilai tertinggi ke-1 dan ke-4

=SUMPRODUCT(LARGE(rangeJumlah,{1,4}))

Sesuai gambar contoh, sel aktif yaitu cell E4 memiliki formula sebagai berikut:

=SUMPRODUCT(LARGE(B4:B13,{1,2}))

Rumus tersebut dapat diterjemahkan sebagai berikut: menjumlahkan nilai terbesar ke-1 dan terbesar ke-2 dari kumpulan nilai yang ada di dalam range B4:B13. Dengan kata lain, dapat juga diartikan sebagai penjumlahan 2 bilangan terbesar dari beberapa bilangan yang ada dalam range B4:B13

Cara Kerja Rumus

Fungsi LARGE berperan untuk mendapatkan nilai tebesar ke-N dari bilangan-bilangan yang ada dalam rangeJumlah (range B4:B13)

= LARGE(B4:B13,1) ⇒ mendapatkan nilai terbesar ke-1 dari range B4:B13

= LARGE(B4:B13,2) ⇒ mendapatkan nilai terbesar ke-2 dari range B4:B13

Karena kita menginginkan beberapa nilai, maka parameter N kita letakan dalam tanda kurang kurawal.

=LARGE(B4:B13,{1,2}) ⇒ Mendapatkan nilai terbesar ke-1 dan ke-2 dari range B4:B13

Rumus ini jika diletakan tersendiri maka akan menghasilkan nilai bilangan terbesar sesuai N pertama dalam array. Contoh rumus di atas akan menghasilkan bilangan terbesar ke-1 (maksimum) dari range B4:B13. Ini dikarenakan angka 1 merupakan elemen pertama dalam array {1,2}. Oleh karenanya, rumus LARGE  harus digabungkan dengan fungsi lainnya supaya kedua nilai dalam array dapat terkalkulasi. Disinilah fungsi SUMPRODUCT memainkan perannya.

Fungsi SUMPRODUCT berperan untuk menjumlahkan semua nilai yang dihasilkan oleh fungsi LARGE

=SUMPRODUCT(LARGE(B4:B13,{1,2}))

Fungsi SUMPRODUCT dalam contoh rumus excel di atas bekerja untuk menjumlahkan bilangan tertinggi ke-1 dan tertinggi ke-2 dalam range B4:B13 yang dihasilkan oleh fungsi LARGE. Dengan menggunakan tabel contoh maka hasilnya adalah 19, merupakah hasil penjumlahan bilangan 10 (nilai terbesar ke-1) dan bilangan 9 (nilai terbesar ke-2)

Silahkan dicoba dan perhatikan hasilnya, jika kita merubah elemen-elemen array, misalnya:

=SUMPRODUCT(LARGE(B4:B13,{1,2}))

=SUMPRODUCT(LARGE(B4:B13,{1,2,3}))

=SUMPRODUCT(LARGE(B4:B13,{1,4}))

Alternative Rumus Lainnya.

Sebagai alternative, kita juga bisa mengganti fungsi SUMPRODUCT dengan fungsi SUM dalam bentuk formula array untuk menjumlah beberapa nilai tertinggi.

Caranya:

Ketik contoh rumus berikut pada sel E4

=SUM(LARGE(B4:B13,{1,2}))

Begitu selesai mengetik rumus kemudian tekan Ctr + Shift + Enter. Hal ini untuk menjadikannya sebagai rumus array.

Maka di formula bar, kita bisa melihat bahwa rumus tersebut berada di dalam tanda kurung kurawal, inilah yang kita kenal sebagai rumus array.

{=SUM(LARGE(B4:B13,{1,2}))}

Catatan: Terlepas dari penjelasan di atas, ternyata kombinasi fungsi SUM dan LARGE juga dapat bekerja dengan baik tanpa harus dalam bentuk formula array.

Demikian pembahasan super singkat mengenai contoh rumus untuk menjumlahkan beberapa nilai tertinggi di excel.  Semoga bermanfaat

Contoh Rumus Excel Lainnya

• Menjumlahkan Berdasarkan Warna di Excel
• Kalkulasi Penjumlahan Total Berjalan (Running Total)
• Penjumlahan Multi Sheet dengan Rumus SUM 3 Dimensi.