14

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?

14

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).

  • 2
    The getBackgroundColor() is deprecated. Perhaps you can update the code? – Jacob Jan Tuinstra Sep 27 '13 at 13:36
  • 2
    @JacobJanTuinstra I have updated the code, but it takes a while for Google to review my submission. In the meantime, you can use this code – Cristian Lupascu Sep 27 '13 at 14:34
  • 2
    @domen yes, there is:github.com/clupascu/GoogleApps_SumByColor/blob/master/…. Not a very pretty solution, but it works. – Cristian Lupascu Nov 30 '13 at 20:25
  • 2
    The script gallery has been replaced by add-ons. I can't find "sum by color" under add-ons. Do you have to submit your script to add-ons for it to be available there? – Jeff Lockhart Jun 29 '14 at 1:48
  • 3
    @w0lf The new Google spreadsheets seem to no longer support the script gallery. It redirects with a message to the new add-ons. The script editor is still available and I used your code there. I made a fork and added support for skipping empty (non-numeric) cells in the sum (otherwise an error occurs if empty cells exist in the range): gist.github.com/jeffdgr8/1553faa6360ad04e9c17 – Jeff Lockhart Jun 30 '14 at 20:45
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();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();

  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  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: http://igoogledrive.blogspot.lt/2015/11/google-spreadsheet-count-of-colored.html

  • I'm getting an error Range Not Found???? :(( – Kegham K. Apr 21 '18 at 23:01
  • im getting TypeError: Cannot call method "pop" of null. (line 13, file "Code")Dismiss – Arturino Jul 3 at 18:27

protected by Community Mar 6 '13 at 15:52

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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