17

I have seen this question and altough I face the same problem, the answer to that question is not helpful to my case.

I have large spreadsheets which are changed frequently. So, according to the solution accepted to that question, it is hard to duplicate all populated columns and it is cumbersome to edit one more cell everytime I wish to make a change on cells background colors.

Isn't there a way to sum data based on a condition regarding cell background color in Google Spreadsheets?

1 Answer 1

2

This is how I got it to work:

  1. Open Extensions > Apps Script

  2. Paste the code below, save script

  3. Go back to your spreadsheet. Use with =countColoredCells(A1:Z5,C5) where C5 is the cell with the color to be count.

Code:

/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function countColoredCells(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rangeA1Notation = formula.match(/\((.*)\,/).pop().trim();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop().trim();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.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=count+1;
  return count;
};

Code credits (changed): http://igoogledrive.blogspot.lt/2015/11/google-spreadsheet-count-of-colored.html

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.