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

118 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
    3. You all got the error because you guys didn't implemented the function in your cells. After you authorize your function goto your sheet and then try it. It'll resolve the issue.

      Delete
    4. yes This suggestion worked!!!! thanks so much

      Delete
    5. This fomula works well!!!! Thanks a lot!

      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
  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
    Replies
    1. me too, what should i do?

      Delete
    2. I has this problem when my formula contained a space in it. see if you have any spaces there, might be the same problem

      Delete
  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
    Replies
    1. Check to make sure the on the script editor on line 1 you delete the auto populated "function myFunction() {" also remove the on the final line of the script "}"

      Effectively the script written above already includes both these parts, hence why you're getting the "Unknown Function" error message.

      Delete
  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
    4. I still have the same issue, even with that formula.
      TypeError: Cannot call method "pop" of null. (line 13, file "countColoredCells")

      Delete
    5. I came here because I was getting that error also- As a work-around, possibly we should 'pre-fill' all the cells with blanks rather than nulls?
      I'll try that. Let you know in a few days if I know it works.

      Delete
    6. I keep getting this error
      SyntaxError: Unexpected token '}' (line 30, file "CC.gs")

      Delete
    7. Works great, just change the ; as delimiters in the regex! :)

      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
  36. I get an error on line 10 with:
    var activeSheet = activeRange.getSheet();

    ReplyDelete
  37. I just used this and when I change the color of the cell, the number (count) doesn't change. Thoughts?

    ReplyDelete
    Replies
    1. Only content change triggers the count, afaik.

      Delete
  38. Can someone help me with the "Range not found (Line XX)" problem? Its always pointing to
    var range = activeSheet.getRange(rangeA1Notation);
    Thanks!

    ReplyDelete
  39. Internal error executing the custom function.

    Did anybody get this error! How to solve this error?

    ReplyDelete
  40. I am wonder how I can get this to apply to all sheets that I create in the future without having to add script every time?

    ReplyDelete
  41. Is there a simple way to add a second condition to match text in another cell in A2

    ReplyDelete
  42. I am getting a null result from both the original code above and Sharky's .pop correction. The code is saved, enabled and running on the spreadsheet but it returns a null.

    The formula is: =countColoredCells(V4:V26,V4)

    There are about 5 different cell colors in the column

    To confirm that it was running and returning a null I wrote this:
    =if(countColoredCells(V4:V25, V4) = "", 0, countColoredCells(V4:V25, V4))
    This formula returns a "0" zero.

    Any ideas?

    ReplyDelete
    Replies
    1. Here is the full script that is running:

      function countColoredCells()
      {
      /**
      * @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;
      };
      }

      Delete
  43. Help! I walked through all of the steps but when I enter the formula on my sheet, I get "NAME?)

    ReplyDelete
  44. Didn't work for me. "Unknown function" error. Any tips?

    ReplyDelete
  45. am getting an error when with this. says "unknown functgion 'countColoredCells

    ReplyDelete
  46. If i would like to count them automatically,how can i do?
    For example, if i add the same color to another cell but it didn't count, how can i input to make it count automatically ?

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

    ReplyDelete
  48. I have an issue where nothing gets outputted, I can see the function is Loading when I change a value in the range but repeatedly nothing gets outputted, it just shows a blank cell.

    ReplyDelete
    Replies
    1. I have the exact same issue. The cells that I have the formula running in show that that they are loading, but then the cells remain blank.

      Delete
    2. Me too, the function is Loading but but then the cells remain blank. some help?

      Delete
  49. The result is not uptading even when I refresh the page.

    ReplyDelete
    Replies
    1. Sorry I have just read "When you change the color of any cell the value will not get auto updated in the result"

      Delete
    2. I guess, you have to re-run the script from script editor maybe

      Delete
  50. Thanks a lot, it works perfectly

    ReplyDelete
  51. I am trying to use in google sheets and can't seem to find the function in google sheets when I type =countcoloredcells, any ideas what I'm missing?

    ReplyDelete
  52. Is there any way to sum the cells of a specific color that have whatever text on it. Here a screenshot
    https://prnt.sc/10pybmx

    The script work like a charm if on cells are numbers, but if you have an alphanumeric data, the script don't sum them.

    I'm looking for the same first idea of script, but I want that sum the total cells that have data of an specific color. For the example on the screenshot that I left above these line, I'm looking for the sum of all yellow, all orange, all blue and pink cells that have any data.

    Regards

    ReplyDelete
  53. Hey,
    Is there a way to sort cells from multiple columns into one empty column if they are colored.
    So I have 7 columns and I am using conditional formatting to mark the duplicates (if a same text comes up in two different columns its marked red, if its comes up in three different columns its yellow etc...
    Since the database is big and most of the names are shown only once (they are not colored in this case) I would like to sort all the colored ones in the empty column next to it.

    Thanks in advance

    ReplyDelete
  54. Thanks for the code. It worked.

    ReplyDelete
  55. the results is not updated when I modified the file only when I reRun the script.
    How it can be automatically ?

    ReplyDelete
  56. if i add new colored cell its not counting automatically

    ReplyDelete
  57. if i add additional new colored cell its not counting automatically

    ReplyDelete
  58. This is very helpful, thank you! Is there any way I can ignore the count from hidden rows?

    ReplyDelete
  59. Formula works but When data changes on sheet used with google form it removes formula from cell any suggestions ??
    cel1 a2 4
    cell b2 erased formula (formula there before form summited)
    cell c2

    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;
    };

    ReplyDelete
  60. I have this error: Exception: Please select an active sheet first.
    countColoredCells @ Sin título.gs:9

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

    ReplyDelete
  62. hello from russia, worked 21.04
    /**
    * @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