Count coloured cells on Google Sheets with reference

In a Google spreadsheet if you need to count cells based on their background color you can use this code snippet in the Google Script editor.

The Google Script editor is already in your Google Sheet, just open the Tools menu and choose Script editor.

function countColoured(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    }
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) {
    throw new Error(args.join('!') + ' is not a valid range');
  } 
  var c = 0;
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      c = c + ( range.getCell(i,j).getBackground() == "#ffffff" ? 0 : 1 ); 
    }
  }
  return c > 0 ? c : "" ;
}

This function is different from other functions found online because this one accepts references, and you don’t have to pass strings with ranges (such as “A1:A100“). In a cell of your sheet you can use =countColoured(A1:A100)

As you can see the function parses the formula in the cell to extract the range of cells to be analyzed. Then it cycles on those cells and count look the background, when different from white (#ffffff) it adds 1.

Finally the total count is returned as the result.

The cells with this formula are automatically refreshed (updated) when values changes, but not when background color changes. So to update the count you have to trigger it manually by changing a cell in the spreadsheet.