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