Thursday, November 12, 2015

Google Spreadsheet Count of colored cells in a range in Custom Function

Count of colored cells in a range in Custom Function



I have the following formula in cell E9:
=countColoredCells(A1:C6,E1)

and the following formula in cell F9:
=countColoredCells(A1:C6,F1)

countColoredCells is a custom function created with the help of the following script code, in this function you need to pass the range in first parameter in which you want to count the colored cells, and also you need to pass the cell in the second parameter which have the background that you need to count in the range (provided in first parameter).

Put the following script code in the script editor of your spreadsheet:

/////////////////////////////////////////////////

/**
* @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;
};

/////////////////////////////////////////////////

NOTE:


When you change the color of any cell the value will not get auto updated in the result,
so you need to temporary change the value of any of one of the cell in the range (and then change it back as you want..)

this will update the color counting result...

For getting sum instead of count of cells having a background color, look at the following link:
http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-sum-of-colored-cells.html

15 comments:

  1. Thank you very much for the help, it helped me a lot!

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. TypeError: No se puede llamar al método "pop" de null .....

    ReplyDelete
  4. Is there a way it can do it automatically??

    ReplyDelete
  5. Everybody who has "TypeError: Cannot call method "pop" of null" error, that is happening because in the code provided match() is looking for "," as a divider between function arguments, but now it is ";".

    So you have to change match(/\((.*)\,/) to match(/\((.*)\;/)

    and

    change match(/\,(.*)\)/) to match(/\;(.*)\)/)

    worked for me

    ReplyDelete
  6. You failed to mention that you need to RUN this formula into your google account, took me a very long time to realize this was causing the error when I entered the formula

    ReplyDelete
  7. Hi,
    could you please suggest a solution how to fix issue during the debug of Sum script? I'd copy-paste and receiving the error
    TypeError: It's not possible to execute method "pop" object null. (line 14, file "SumColoredCells")
    Actually the code: var rangeA1Notation = formula.match(/\((.*)\,/).pop();

    ReplyDelete
    Replies
    1. Hi,

      For some reason it is still not working for me, I have changed the match that I was supposed to, and it still does not pop up when I type =SUM

      Delete
  8. Nothing I have changed to the script has worked for me. it doesnt say error when I run it, but does not show up when I try to use it in the cell

    ReplyDelete
  9. I kept having the same error, this one worked for me;
    function countColoredCells(countRange,colorRef) {
      var activeRg = SpreadsheetApp.getActiveRange();
      var activeSht = SpreadsheetApp.getActiveSheet();
      var activeformula = activeRg.getFormula();
      var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
      var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
      var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
      var BackGround = activeSht.getRange(colorRefAddress).getBackground();
      var countCells = 0;
      for (var i = 0; i < backGrounds.length; i++)
        for (var k = 0; k < backGrounds[i].length; k++)
          if ( backGrounds[i][k] == BackGround )
            countCells = countCells + 1;
      return countCells;
    };

    ReplyDelete