Menjumlahkan 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 .

Cara Menjumlah Cell Berdasarkan Warna di Excel-sebelumnya kita mungkin sudah biasa menjumlah cell dalam excell berdasarkan kriteria tertentu menggunakan rumus SUMIF.

namun ternyata saat ini, rumus fungsi SUMIF belum bisa mengeksekusi secara langsung penjumlahan cell pada excell berdasarkan warna.

Bagaimana cara menjumlah berdasarkan kriteria warna cell dengan cepat? mari kita lihat tutorial berikut :

untuk bisa menjumlahkan berdasarkan warna pada excel ada beberapa trik.

kali ini kita memilih menggunakan metode membuat Fungsi sendiri. fungsi sendiri ini biasa disebut dengan istilah UDF atau (User Define Function)

bagaimana cara nya membuat UDF (User Define Function) ?

untuk membuat fungsi UDF silahkan salin script berikut pada Visual basic Editor.

Function SUMWarna(ByVal SumRange As Range, _
ByVal SumCriteria As Range) As Double
Rem Auth    : wahyuddinrosi.com
Dim RangeCell As Range
For Each RangeCell In SumRange
    If RangeCell.Interior.Color = SumCriteria.Interior.Color Then
        SUMWarna = SUMWarna + RangeCell.Value
    End If
Next
End Function

Time needed: 3 minutes.

Cara Membuat UDF SUMWarna pada Visual Basic Editor

1. Buka Visual Basic Editor

   silahkan buka Visual Basic Studio dengan menekan shorcut key ALT + F11

2. Insert > Module
   ]
   

3. Paste Script berikut

   pada kolom editor paste script diatas

Menerapakan Rumus Penjumlahan Berdasarkan Warna

selanjutnya, setelah membuat fungsi baru pada VBA maka selanjutnya kita masukkan rumus fungsi tersebut

cara menggunakan nya sama seperti kita menggunakan rumus seperti biasa :

=SUMWarna(Range,Range_Kriteria)

''Range   = Range yang akan dijumlahkan
''Range_Kriteria = Cell yang warnanya dijadikan kriteria 

untuk lebih jelas nya bisa anda lihat pada gambar berikut :

so itu tadi diatas bagaimana cara menjumlah cell pada excell menggunakan fungsi UDF

silahkan download file spreedsheet contoh berikut.

Bagaimana cara menghitung warna di excel?

Bagaimana rumus countif?

Bagaimana cara membuat garis di excel?