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

61 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
    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
    2. I got the same error message but the function still worked in my sheets. Thanks for edit!

      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
    Replies
    1. =countColoredCells(J$4:J$40,$K$1:$N$1)

      Here I am counting colored cells in column J from rows 4 t0 40 using 4 different colors in reference cells K1, L1, M1, N1.

      Well at least I thought it was however...

      It stops after counting the first color

      oops.

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

    ReplyDelete
    Replies
    1. If you mean to auto-refresh the count, in Sheets script editor you can define a Trigger to refresh the screen. See: Edit > All your triggers -- Run=; Events=From spreadsheet; action=On edit. Note however this will only refresh when cell data changes, not when when background color changes. Therefore you can consider using Conditional Formatting to set cell color when values are updated to have update/cell color changes more closely linked.

      Delete
  15. i have an error:
    Range not found (line 19).

    ReplyDelete
  16. My Error notes below?

    missing ) after formal parameteres. (line 8, file "Code")

    ReplyDelete
  17. I fixed my issue thanks - had wrong formatting pasted, Can someone help on counting more than 2 colors? the formula says error line 19 (out of range) when I add a 3rd color to count in 1 column - thank u so much

    ReplyDelete
  18. Truly helpful! Thank you a lot :)

    ReplyDelete
  19. error as Name is showing..can somebody help.
    where do I have to put "Code" as stated by Jason above

    ReplyDelete
  20. I am getting an error message -> Unknown Function. Please help

    ReplyDelete
  21. TypeError: Impossibile chiamare il metodo "pop" di null. (riga 12, file "Codice")

    help me

    ReplyDelete
  22. Is it possible to do a "countColoredCellsIF"? This count formula worked for me, but I need another criterion in my formula.

    ReplyDelete
  23. Hello,

    I am also still getting this error message :
    TypeError: Cannot call method "pop" of null. (line 13, file "Code")
    I have changed the comas (,) to semi-colons (;) and the error remains.

    Please help.

    ReplyDelete
  24. I debugged it and it works using the following. Hope it helps someone.

    /**
    * @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().replace(/\s/g, "");
    var range = activeSheet.getRange(rangeA1Notation);
    var bg = range.getBackgrounds();
    var values = range.getValues();

    var colorCellA1Notation = formula.match(/\,(.*)\)/).pop().replace(/\s/g, "");
    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;
    };

    ReplyDelete
    Replies
    1. Hey Sharkey,

      I'm still getting an error on the line 13 TypeError: Cannot call method "pop" of null. (line 13, file "SumColouredCells. Seems like the same one everyone else is getting. Any suggestions?

      Delete
    2. It resolved my "Range not found (Line 20)" error. Many thanks!

      Delete
    3. This is so good!! It helped my formula

      Delete
  25. Hi all, tried every single fix suggested here, including last one by Sharky, getting this message:
    TypeError: Cannot call method "pop" of null. (line 13, file "countColoredCells")
    Any help would be much appreciated

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

    ReplyDelete
  27. The original one worked for me as an owner of the spreadsheet but failed when I tried to implement it on an spreadsheet owned by someone else! Any thoughts?

    ReplyDelete
  28. I know it is super stupid advice BUT it works with me from time to time. I do get the pop problem and then I go to do sth else and when I come back to the sheet it is ok. So it seems to be working everytime I copy the above text but then gets bugged. No idea how time (or what factors actually) play important roles here.

    ReplyDelete
  29. It worked perfect, can you expand this formal to also include an additional criteria like a word

    ReplyDelete
  30. mine is working but after second cells it stop counting and just show 7 as count result. any help?

    ReplyDelete
  31. Incorrect count is displayed i.e. 180. However, when I count it manually it is 19

    ReplyDelete
  32. It works fine for me with small ranges. But in large ranges it keeps displaying 'loading'.

    ReplyDelete
  33. Hi,

    First of all, thanks! This is awesome!

    I was wondering if there is a way to use this formula with an additional condition, for example, count all yellow cells except the ones with duplicate values. It is regarding a spreadsheet with names and I would like to only count each name once even though some appear multiple times (there is a reason for that and I can't change it).

    Maybe with a countif modification?

    TIA!

    ReplyDelete
  34. Is there any way to get this to run without having to change a value? just if the cell color is changed?

    ReplyDelete
  35. It works but sometimes I get this error in the spreadsheet cell "result was not a number"

    ReplyDelete