Google-sheets – Calculating shaded cells

formulasgoogle sheetsgoogle-apps-scriptgoogle-sheets-custom-function

I am using Google Spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into.

Is there a formula to calculate the number of cells in a column that are shaded (or not shaded) a certain color?

For example: 20 of the cells are gray and 31 are not gray (shaded any other color) and instead of manually counting them, it calculates this at the bottom of reach column.

Best Answer

I have found a way to do this, using the scripting functionality that Google Spreadsheet provides.

Here's how to do it:

  • open your spreadsheet
  • in the menu, go to Tools -> Script Editor...; this will open a new window that allows you to enter code
  • copy the code below
  • paste the code in the Script Editor window and press CTRL+S to save it
  • close the Script editor windows and go back to the spreadsheet

Code:

function getBackgroundColor(rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getRange(rangeSpecification);
  return cell.getBackground();
}

function sumWhereBackgroundColorIs(color, rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeSpecification);
  var x = 0;
  for (var i = 1; i <= range.getNumRows(); i++) {
    for (var j = 1; j <= range.getNumColumns(); j++) {
      var cell = range.getCell(i, j);
      if(cell.getBackground() == color)
        x += parseFloat(cell.getValue());
    }
  }
  return x;
}

function countCellsWithBackgroundColor(color, rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeSpecification);
  var x = 0;
  for (var i = 1; i <= range.getNumRows(); i++) {
    for (var j = 1; j <= range.getNumColumns(); j++) {
      var cell = range.getCell(i, j);
      if(cell.getBackground() == color)
        x++;
    }
  }
  return x;
}

After following the above steps, you have three more functions available in the spreadsheet:

  • countCellsWithBackgroundColor(<color>, <range specification>)
  • sumWhereBackgroundColorIs(<color>, <range specification>)
  • getBackgroundColor(<cell specification>)

Please note that <range specification> and <cell specification> are expressed in A1 notation, and must be enclosed in quotes.

For example, to get the count of all the cells in the range B2:F13 that have the background color set to white, you should enter the following formula:

=countCellsWithBackgroundColor("white", "B2:F13")

and to compute the sum of the same cells, use the formula:

=sumWhereBackgroundColorIs("white", "B2:F13")

Some cells may not have the background set to a color such as 'white', 'gray', but a RGB color like #6fa8dc. You cannot guess what the color is, so if you want to find out the color for a cell (for example, B9), you should enter this formula in a cell:

=getBackgroundColor("B9")

and afterwards use this value as a parameter to the two functions above.

It appears that if a cell's background color is a "theme color," getBackground() incorrectly always returns #ffffff. I haven't found a way around this other than to use a color from the standard set.

Again, note the quotes in all above examples; without them, the formulas would not work.

Related Topic