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.

Penjumlahan Berdasarkan Warna di Excel

Artikel ini membahas penjumlahan berdasarkan kriteria warna di excel. Untuk mengetahui cara menghitung jumlah sel dengan warna tertentu dapat dilihat di artikel Menghitung Jumlah Sel Berdasarkan Warna:

Penjumlahan berdasarkan kritera warna di excel dapat dilakukan dengan beberapa alternative rumus. Salah satunya yang paling umum adalah menggunakan fungsi SUMIF. Namun selain SUMIF, kita juga bisa menggunakan fungsi lainnya untuk menjumlahkan berdasarkan kriteria warna, antara lain: fungsi SUMIFS, fungsi SUMPRODUCT dan fungsi SUM (dalam bentuk rumus array).

Contoh:

Rumus Umum:

=SUMIF(rangeWarna,warna,rangeJumlah)

• rangeWarna = range kolom berisi kriteria warna

• warna = jenis warna yang akan dihitung jumlahnya

• rangeJumlah = range kolom yang berisi jumlah yang akan dihitung totalnya

Penjelasan

Rumus SUMIF akan melihat konten pada rangeWarna, kemudian mencocokan dengan kriteria  warna. Jika cocok maka rumus ini akan menjumlahkan data dari sel sejajar di kolom rangeJumlah. 

Pada saat mengetik argumen rangeWarna dan rangeJumlah sangat disarankan menggunakan referensi absolute. Ini sangat penting supaya referensi tetap dan tidak bergeser pada saat rumus di copy paste ke sel di bawahnya.

Untuk menjadikan referensi absolute, yaitu dengan cara mengetik tanda dolar ($) sebelum komponen kolom dan komponen baris pada nama range default. Selain itu, tanda absolute dolar bisa ditambahkan ke nama range default dengan cara memposisikan kursor bagian nama range di formula bar, kemudian menekan F4 satu kali.

Cara Kerja Rumus

Perhatikan contoh rumus dalam gambar di atas yaitu:

=SUMIF($B$4:$B$13,E4,$C$4:$C$13)

Dari rumus tersebut kita bisa mengetahui komponen argumen rumus sebagai berikut:

• rangeWarna ⇒ range kolom berisi data warna yaitu range $B$4:$B$13, merupakan bentuk absolute dari range B4:B13
• warna ⇒ sel berisi kriteria warna sebagai kata kunci, yaitu sel E4.  Kriteria ini dalam bentuk referensi relative (tanpa tanda dolar) sehingga jika dicopy ke sel di bawahnya, referensi akan ikut bergeser menjadi sel di bawahnya yaitu sel E5 yang berisi kriteria warna lainnya.
• rangeJumlah ⇒ range kolom berisi bilangan yang akan dijumlahkan nilainya, yaitu range $C$4:$C$13 yang merupakan bentuk absolute dari range C4:C13

Alternative Rumus Lainnya 

Selain menggunakan fungsi SUMIF, kita juga bisa melakukan penjumlahan berdasarkan kriteria warna menggunakan fungsi excel lainnya seperti SUMIFS, SUMPRODUCT dan rumus Array.

Menggunakan fungsi SUMIFS

=SUMIFS(rangeJumlah,rangeWarna,warna)

=SUMIFS($C$4:$C$13,$B$4:$B$13,E4)

Menggunakan fungsi SUMPRODUCT

=SUMPRODUCT((rangeWarna=warna)*rangeJumlah)

=SUMPRODUCT(($B$4:$B$13=E4)*$C$4:$C$13)

Menggunakan rumus array (tekan Ctrl + Shift + Enter  setelah selesai mengetik rumus).

Rumus ini sebenarnya menggunakan fungsi SUM, hanya saja dalam bentuk rumus array.

{=SUM((rangeWarna=warna)*rangeJumlah}

{=SUM(($B$4:$B$13=E4)*$C$4:$C$13)}

Keterangan rumus array: tanda kurung kurawal {} pada rumus array jangan diketik secara manual karena tanda tersebut akan muncul secara otomatis begitu kita menekan Ctrl + Shift + Enter .

Demikian pembahasan singkat mengenai contoh rumus untuk penjumlahan berdasarkan kriteria warna. Semoga bermanfaat.

Contoh Rumus Excel Lainnya:

• Kalkulasi Penjumlahan Total Berjalan
• Rumus SUM 3D Untuk Penjumlahan Multi Sheet
• Membuat Histogram Menggunakan Rumus Excel

Kalkulasi Penjumlahan Total Berjalan

Kalkulasi penjumlahan total berjalan atau running total merupakan operasi penjumlahan yang terus berlangsung mengikuti periode data yang sedang berjalan. Operasi penjumlahan ini biasanya digunakan untuk mengetahui seberapa besar nilai yang diperoleh sampai dengan saat ini, dimulai dari periode tertentu.

Contoh:

Rumus penjumlahan total berjalan menggunakan fungsi SUM digunakan untuk mengkalkulasi berapa besar total nilai pendapatatan sampai dengan bulan berjalan dimulai bulan Januari.

Rumus Umum

=SUM($A$1:A1)

Penjelasan

Rumus SUM untuk kalkulasi total berjalan harus menggunakan referensi range absolute sebagian. Referensi absolute diterapkan pada sel yang merupakan titik pertama atau start proses kalkulasi. Referensi absolute sebagian ini dimaksudkan supaya ketika rumus di copy ke sel di bawahnya, referensi sel awal tidak berubah, namun referensi sel kedua akan berubah mengikuti data berjalan. Sehingga setiap baris akan menghasilkan jumlah total sampai dengannya atau running  total.

Cara Kerja Rumus.

Perhatikan contoh rumus =SUM($C$4:C4) yang digunakan untuk mengkalkulasi total jumlah pendapatan sampai dengan bulan berjalan. Rumus tersebut diketik di sel D4.

Dapat kita lihat, referensi yang digunakan yaitu $C$4:C4. Kenapa tidak diketik C4:C4  atau $C$4: $C$4. Sesuai penjelasan di awal, $C$4:C4 merupakan contoh referensi yang bersifat absolute sebagian. Ini dimaksudkan ketikan rumus yang menggunakan referensi ini, ketika dikopi ke sel di bawahnya maka referensi $C$4 tidak akan berubah. Sehingga rumus  akan mengikuti pola sesuai nomor urut di tabel seperti berikut

• Tabel Baris ke-1 =SUM($C$4:C4)
• Tabel Baris ke-2 =SUM($C$4:C5)
• Tabel Baris ke-3 =SUM($C$4:C6)
• Tabel Baris ke-4 =SUM($C$4:C7)
• Tabel Baris ke-5 =SUM($C$4:C8)
• Tabel Baris ke-6 =SUM($C$4:C9)
• Tabel Baris ke-7 =SUM($C$4:C10)

Dan begitu seterusnya, ketika  rumus dicopy ke sel dibawahnya maka referensi sel pertama dalam range tidak berubah, sedangkan referesnsi sel terakhir bersifat relative sehingg akan berubah mengikuti baris penempatan rumus.

Contoh Penerapan Rumus Total Berjalan Lainnya:

Selain menggunakan fungsi SUM untuk menghitung total jumlah pendapatan sampai dengan bulan berjalan. Referensi semi absolute juga bisa diterapkan untuk fungsi lainnya, 

Misalnya

Kalkulasi nilai rata-rata pendapatan sampai dengan bulan berjalan

=AVERAGE($C$4:C4)

Mengetahui nilai pendapatan maksimum sampai dengan bulan berjalan

=MAX($C$4:C4)

Mengetahui nilai pendapatan minimum sampai dengan bulan berjalan

=MIN($C$4:C4)

Mengetahui berapa kali pendapatan melebihi nilai tertentu sampai dengan bulan berjalan. Contoh: pendapatan lebih dari 3.000.000

=COUNTIF($C$4:C4,">3000000")

Mengetahui berapa kali pendapatan kurang dari nilai tertentu sampai dengan bulan berjalan. Contoh: pendapatan lebih kecil dari 3.000.000

=COUNTIF($C$4:C4,"<3000000")

Serta ada banyak rumus-rumus kalkulasi total berjalannya yang dapat kita buat. Silahkan diexplorasi lebih lanjut.

Demikian pembahasan singkat mengenai contoh rumus excel untuk kalkulasi total berjalan (running total). Semoga bermanfaat.

Contoh Rumus Exel Lainnya:

• Rumus SUM 3 Dimensi Untuk Penjumlahan Multi Sheet
• Membuat Histogram Menggunakan Rumus Excel
• Menghitung Banyaknya Data Tertentu
• Menghitung Jumlah Hari Tertentu

Rumus SUM 3 Dimensi Untuk Penjumlahan Multi Sheet

Rumus SUM multiple sheet dengan referensi 3 dimensi bisa digunakan untuk menjumlahkan nilaid ata pada tabel sejenis yang terletak dalam beberapa sheet yang posisinya berurutan di dalam lembar kerja excel. Rumus ini sangat dianjurkan karena fleksibilitasnya dan kepraktisannya. Formula tidak dipengaruhi oleh berapa banyak sheet sebagai referensi, sehingga tidak diperlukan editing rumus ketika ada penambahan sheet referensi.

Contoh :

Rumus Umum:

=SUM(SheetAwal:SheetAkhir!A1)

Penjelasan:

Rumus SUM untuk menjumlahkan semua nilai data yang terletak di sel A1 pada sheet1, sheet2,sheet3 dan sheet4 yang posisi sheetnya berurutan, biasanya dituliskan sebagai berikut:

=SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1, Sheet2!A1)

Rumus diatas sebenarnya bisa dipersingkat penulisannya dengan mengunakan apa yang dikenal sebagai referensi 3 dimensi. Sehingga rumusnya dapat dituliskan menjadi:

=SUM(Sheet1!A1:Sheet4!A1)

Dalam hal ini Sheet1 bertindak sebagai SheetAwal dan Sheet4 sebagai SheetAkhir. SheetAwal posisinya harus berada paling kiri, sedangkan SheetAkhir harus berada paling kanan. 

Contoh Rumus

=SUM(Minggu1:Minggu4!C3)

Rumus tersebut akan menjumlahkan setiap nilai yang ada pada sel C3 pada sheet Minggu1 sampai dengan sheet Minggu4. Secara konvensional, rumus di atas biasanya dituliskan sebagai berikut:

=SUM(Minggu1!C3,Minggu2!C3,Minggu3!C3,Minggu4!C3)

Dan Kebanyakan Pengguna Excel mungkin lebih sering menggunakan rumus penjumlahan berikut:

=Minggu1!C3+Minggu2!C3+Minggu3!C3+Minggu4!C3

Dari ketiga contoh rumus tersebut, kita bisa mengetahui bahwa rumus =SUM(Minggu1:Minggu4!C3) merupakan yang paling pendek dan tidak dipengaruhi ada berapa banyak jumlah sheetnya, cukup menentukan mana sheet awal dan mana sheet akhir. 

Bukti bahwa rumus SUM 3 Dimensi tidak akan bertambah panjang dengan semakin banyaknya referensi sheet.

• Silahkan tambahkan sheet diantara sheet Minggu 1 dan Minggu 4, sebanyak yang anda mau. 
• Kemudian tambahkan tabel yang serupa pada masing-masing sheet yang anda tambahkan. 
• Terakhir : lihat hasil pada sheet Summary.
• Maka anda akan melihat bahwa jumlah total akan bertambah otomatis sesuai data sheet yang anda tambahkan. Hebatnya lagi, anda tidak perlu mengedit rumus pada sheet Summary.

Contoh Penerapan Referensi 3 Dimensi Lainnya.

Selain fungsi SUM, referensi 3D juga dapat diterapkan pada fungsi lainnya.

Misalnya:

Menghitung nilai rata-rata poin minggu 1 s.d minggu 4

=AVERAGE(Minggu1:Minggu4!C3)

Menghitung nilai poin maksimum (terbesar) pada minggu 1 s.d minggu 4

=MAX(Minggu1:Minggu4!C3)

Menghitung nilai poin minimum (terkecil) pada minggu1 s.d minggu 4.

=MIN(Minggu1:Minggu4!C3)

Catatan Penting:

• Kita dapat mengabaikan perhitungan dari sebuah sheet dengan cara menggeser sheet tersebut keluar dari referensi 3 D. Misalnya coba saja pindahkan sheet Minggu2 ke sebelah kanan sheet summary, maka otomatis rumus di sheet summary tidak akan menjumlahkan nilai yang ada di sheet Minggu2.
• Hati-hati dalam menempatkan sheet summary, karena jika sheet tersebut digeser ke posisi antara referensi 3 D, (misalnya diantara minggu3 dan minggu4) maka akan terjadi error  circular reference.

Contoh Rumus Excel Lainnya

• Membuat Histogram Menggunakan Rumus Excel
• Rumus Excel Jika Minus Maka Nol
• Merubah Angka Menjadi Bulan
• Merubah Tanggal Menjadi Hari dan Bulan