How to Sum Cells by Background Color in Google Sheets (Without Add-ons)

Google Sheets is packed with powerful features, but one thing it doesn’t do out of the box is sum values based on cell background color. I needed to use this on a personal Sheet. Here is how we can achieve this using a Google Apps Script.

How to easily sum cells with a specific background color (like green) using a simple Google Apps Script. No third-party add-ons, no complicated workarounds

1. Open your Google Sheet.
2. Go to Extensions > Apps Script.
3. Delete any code in the editor and paste the following:

function sumByColor(color, rangeString) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange(rangeString);
  var bgColors = range.getBackgrounds();
  var values = range.getValues();
  var total = 0;

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (bgColors[i][j] == color) {
        if (!isNaN(values[i][j])) {
          total += Number(values[i][j]);
        }
      }
    }
  }
  return total;
}

Save and close. In you Sheet, in a cell use:

=sumByColor("#b6d7a8", "A1:A300") 

• Replace #b6d7a8 with the hex code of the colour used in your cells.
  This is the second default green named “light green 2” by the way.
• Adjust the range (A1:A300) as needed.

I hope this simple workaround can help someone!