Sum data based on cell color in Google Spreadsheets

Question

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?

Answer 1

Please see if my answer to a similar question helps.

Update:

My submission for the Google Docs Script gallery was accepted and you can install it to get the functionality you wish.

The steps:

• Open your spreadsheet
• In the menu go to Tools -> Script gallery...
• Search for Sum by color
• Click Install
• Click the Authorize button if you trust the script and then Close
• go back to the spreadsheet

Now you have three additional functions you can use in your spreadsheet formula:

• sumWhereBackgroundColorIs(color, rangeSpecification)
• sumWhereBackgroundColorIsNot(color, rangeSpecification)
• getBackgroundColor(rangeSpecification)

Please from that the rangeSpecification parameters require quotes (") around them.

For example:

=sumWhereBackgroundColorIs("white", "A1:C4")

sums the values of all cells in the A1:C4 range that have a white background.

Please note that if you don't know the color of a certain cell you can use the provided getBackgroundColor function to find out what the color is. This function is necessary, because some colors are expressed as RGB codes (for example, #00ff00 instead of green).

Answer 2

This is how I got it to work:

1. Open Tools > Script Editor

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