Rumus Menghitung Cell Berdasarkan Warna Di Google Sheets

Beberapa pengguna Google sheet masih banyak yang menggunakan warna sebagai pembeda kriteria pada datanya.

Dimana seharusnya hal ini sebaiknya dihindari. Sebagai gantinya, daripada menggunakan warna pembeda, bisa gunakan sebuah nilai pada kolom tertentu untuk memudahan pengolahan data.

Kenapa penggunaan warna tidak di rekomendasikan, salah satu alasannya adalah sampai tulisan ini dibuat belum ada satupun rumus bawaan yang bisa membaca warna.

Sehingga baik itu di Excel ataupun di Google sheet, rumus untuk menghitung berdasarkan warna harus dibuat dengan script.

Biasanya dibuat menggunakan VBA jika Excel, dan di Google sheets, bisa menggunakan Appscript.

Misalnya dibawah ini ada contoh kasus seperti ini :

Untuk menghitung jumlah dan total uang berdasarkan Warna dalam google sheets, karena tidak ada rumus bawaan yang bisa membaca warna, maka, bisa dibuat sebuah UDF (User Defined Function) menggunakan Appscript.

Menghitung COUNT Berdasarkan Warna

Bagian yang pertama untuk mengisi Jumlah, kita bisa membuat sebuah Fungsi Count berdasarkan warna menggunakan Appscript.

Untuk sintaknya bisa dibuat seperti dibawah ini

function countWarna(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rng = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rng);
  var bg = range.getBackgrounds();
  
  var rwarna = formula.match(/\,(.*)\)/).pop();
  var rangew = activeSheet.getRange(rwarna);
  var color = rangew.getBackground();
  
  var count = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        count += 1;
  return count;
}

Simpan script diatas pada script Editor kemudian Save.

Untuk penggunaan Fungsi CountWarna, sama seperti rumus biasa, gunkan formula pada sheet seperti dibawah ini.

Menghitung SUM Berdasarkan Warna

Jika ingin menghitung isi dari cell yang berwarna kita bisa ubah dari menghitung +1 menjadi menghitung berdasarkan isinya.

Scriptnya masih tidak jauh berbeda, yang berbeda hanya nilai yang ditambah saja.

Untuk Script SUM berdasarkan warna, bisa dibuat seperti dibawah ini

function sumWarna(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rng = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rng);
  var bg = range.getBackgrounds();
  var values = range.getValues();

  var rwarna = formula.match(/\,(.*)\)/).pop();
  var rangew = activeSheet.getRange(rwarna);
  var color = rangew.getBackground();
  
  var wsum = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        wsum += values[i][j];
  
  return wsum;
}

Untuk penggunaan rumus ini masih sama, gunakan SumWarna pada formula bar, contohnya bisa dilihat dibawah ini

Itulah solusi yang bisa digunakan untuk menghitung cells berdasarkan warna di Google Sheet.