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

25 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
    Replies
    1. I tried that and still got the same error. Any other suggestions?

      Delete
  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
    Replies
    1. I tried that also and it gave me the same error except line 13 instead of 14. I tried the above fix of changing , to ; and that did nothing also. Any other suggestions?

      Delete
  10. Anyone know why when I click on "script editor..." it takes me to a page that says

    "Sorry, unable to open the file at this time.

    Please check the address and try again.

    Get stuff done with Google Drive

    Apps in Google Drive make it easy to create, store and share online documents, spreadsheets, presentations and more."

    ReplyDelete
    Replies
    1. Too many google accounts logged in.
      I got that when I was logged in on account 1 but I was using googlesheets on account 2 and trying to open script editor on account 2.
      Log out of all google accounts then log in on the one you will be using the sheets and script editor on and it should work.

      Delete
  11. It is possible to pick a reference cell outside the range?
    I mean, make a reference cell in any other place to compare with the range.

    ReplyDelete
    Replies
    1. That's what I've done and it's working fine.

      Delete
  12. This comment has been removed by the author.

    ReplyDelete
  13. If i want to take as inputs 2 reference cells, how can i do it? Thank u!

    ReplyDelete
  14. Fabulous.... just wondering if there is yet an automation option in sheets?

    ReplyDelete