Wednesday, November 11, 2015

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

Sum of a colored cells in a range in Custom Function



I have the following formula in cell E9:

=sumColoredCells(A1:C6,E1)

and the following formula in cell F9:

=sumColoredCells(A1:C6,F1)

sumColoredCells 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 sum the values of the colored cells, and also you need to pass the cell in the second parameter which have the background that you need to sum in the range (provided in first parameter).

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


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


/**

* @param {range} sumRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in sumRange
* @return {number}
* @customfunction
*/

function sumColoredCells(sumRange,colorRef) {

  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula().toString();
  formula = formula.replace(new RegExp(';','g'),',');
  
  
  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 total = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        total=total+(values[i][j]*1);
  return total;
};

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

NOTE:


When you change the color of any cell the value will not get auto updated in the sum 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 colored cells sum result...

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

101 comments:

  1. Great!
    I really like the count and total custom functions!
    Could you also make a custom average function based on the same principles?

    ReplyDelete
  2. Excellent code! One modification that I would like to see is getting the range of another spreadsheet, it errors on doing that.

    ReplyDelete
  3. I can't get this to work. Can someone help mw?

    ReplyDelete
    Replies
    1. Still having problems Lauren? What exactly is happening?

      Delete
    2. Is my script correct?

      function sumColoredCells(A1:K85,yellow) {
      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 total = 0;

      for(var i=0;i<bg.length;i++)
      for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
      total=total+(values[i][j]*1);
      return total;
      };

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

    ReplyDelete
  5. Hey Seats for Fans, thank you very much for this nice solution. However, when trying to implement it I receive an error mistake regarding the pop method which is not allowed to be 0 (line 13 & 19). Can you help me here? Thanks a lot!

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

    ReplyDelete
  7. I am getting a Circular dependency error! Help!

    ReplyDelete
    Replies
    1. What do you write in your cell E9?
      E9=sumColoredCells(A1:C6,E1)

      E1 is the reference color.

      Delete
  8. It says ERROR. Can someone help me?

    ReplyDelete
  9. It's giving an error, can someone help me with this?

    TypeError: Cannot call method "pop" of null. (line 14, file "SumColouredCells")

    ReplyDelete
    Replies
    1. The error occurs because your localization uses the ";" instead of ",". You need to start debugging on the macro page, it will find all the lines in which you need to replace the "," sign with ";". After that, the script works correctly.
      Sorry for my poor English))

      Delete
    2. Михаил Гончаров This is not true... If I change it there is still the problem with calling "pop"

      Delete
    3. Change these lines:
      var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();
      var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();

      Delete
  10. The script works for me. The only inconvenience is that the sum does not get updated automatically, which I hope it has a solution by now considering this script has been published for so many years....Google.......

    ReplyDelete
    Replies
    1. Im with Hallilujah, the script works as advertised, but could there be the addition of Autosum?

      Delete
    2. There is a workaround. I've been playing with this code to figure it out (a little commenting in the code would have been helpful BTW...especially formula.match). I also needed the total to update if data changed.

      You simply need to reference any unused cell as part of a formula which includes sumColoredCells. So =AnyUnusedCell + sumColoredCells(SumRange, colorCellRange). The result (since this is an unused cell) will just be the result of the function. But since you have used a cell reference in the formula (AnyUnusedCell), Google Sheets has to reevaluate it anytime a cell value in the spreadsheet is changed. So, the function will then recalculate and will show the updated information.
      Hope this helps!

      Delete
    3. sorry, I don't understand the workaround method, can you guide more details? many thanks.

      Delete
  11. The error
    TypeError: Cannot call method "pop" of null. (line 13, file "Code")
    It's always happening, what can be done so it will not happen?

    ReplyDelete
  12. The error TypeError: Cannot call method "pop" of null. (line 13, file "Code")

    It's always happening, what can be done so it will not happen?

    ReplyDelete
  13. 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. Thanks! It has solved the issue.

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

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

      Delete
  14. Hi, nope, is not working, the "TypeError: Cannot call method "pop" of null" still happening

    ReplyDelete
  15. How do you make the cells update automatically?

    ReplyDelete
  16. 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
  17. This comment has been removed by the author.

    ReplyDelete
  18. Hi,
    I have the same problem. Has anyone figured out the solution?

    ReplyDelete
  19. Any luck? @Filipp Makarov fix isn't working anymore.

    ReplyDelete
    Replies
    1. I have updated two lines in code (colored in green):

      var formula = activeRange.getFormula().toString();
      formula = formula.replace(new RegExp(';','g'),',');

      Delete
  20. Hi, I have this error

    The error TypeError: Cannot call method "pop" of null. (line 13, file "Code")

    Somebody can help me with this issue? Thanks XD

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

    ReplyDelete
  22. Got a solution for initial tasks:
    1. count amount of green cells;
    2. count sum of values of green cells.
    Code and leaving comments are available at GitHub:
    https://github.com/tooHotSpot/googleSheetsTrick/blob/master/Code.gs

    ReplyDelete
    Replies
    1. The count function works properly but not the sum...

      Delete
  23. Hello, if i want to input two different colours, how could i do that without getting error about the range?

    ReplyDelete
  24. I have the same problem. I would like to use various colors as my color references but the function only seems to take one cell into account as reference

    ReplyDelete
  25. This is awesome! Really saved me.

    Any chance, though, that this can be modified to be a 'sum by font colour' too? Thank you!

    ReplyDelete
  26. I get an error.
    I used
    =sumColoredCells(N4:N15,N4)
    and I get return #NAME?

    ReplyDelete
    Replies
    1. Same with me. Anyone know what I am doing wrong?

      Delete
    2. I had to remove the very first piece of code google put into the script page. "function myFunction()" Keep the "{" part

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

      Delete
    4. Just use =sumColoredCells(N4:N15;N4) instead of =sumColoredCells(N4:N15,N4)

      Delete
  27. I am getting an error that states: Error
    Range not found (line 21). I copied and pasted the code. Any suggestions?

    ReplyDelete
    Replies
    1. I got this error too, the lines in green doesnt work for me

      Delete
  28. Hi, it gives me error - Range not found.... what should I do? Is the script ok? I made copy and paste from above. Please help! Thanks :)

    {
    /**
    * @param {range} sumRange Range to be evaluated
    * @param {range} colorRef Cell with background color to be searched for in sumRange
    * @return {number}
    * @customfunction
    */

    function sumColoredCells(sumRange,colorRef) {
    var activeRange = SpreadsheetApp.getActiveRange();
    var activeSheet = activeRange.getSheet();
    var formula = activeRange.getFormula().toString();
    formula = formula.replace(new RegExp(';','g'),',');


    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 total = 0;

    for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
    if( bg[i][j] == color )
    total=total+(values[i][j]*1);
    return total;
    };
    }

    ReplyDelete
  29. it works with
    =sumColoredCells(C2:C9;C3)

    ReplyDelete
    Replies
    1. it changes back to (C2:C9,C3), and continues to give the same error regarding the "range not found".... thank you!

      Delete
  30. What would you change in the script to sum multiple colors in one function? Thank you.

    ReplyDelete
  31. Hi!
    Thanks for the script! Im trying to sum time values and the script it self works but it results in a weird format. Any idea how to edit the script to be able to sum time values correctly?

    ReplyDelete
  32. I debugged this and got it working using the following code. I hope it helps someone.

    /**
    * @param {range} sumRange Range to be evaluated
    * @param {range} colorRef Cell with background color to be searched for in sumRange
    * @return {number}
    * @customfunction
    */

    function sumColoredCells(sumRange,colorRef) {
    var activeRange = SpreadsheetApp.getActiveRange();
    var activeSheet = activeRange.getSheet();
    var formula = activeRange.getFormula().toString();
    formula = formula.replace(new RegExp(';','g'),',');

    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 total = 0;

    for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
    if( bg[i][j] == color )
    total=total+(values[i][j]*1);
    return total;
    };

    ReplyDelete
    Replies
    1. I didn't look what you fixed but your script is the only one that finally worked! Thanks

      Delete
    2. Thank you very much friend, ... you are the best !!!

      Delete
    3. Thank you very much friend, ... you are the best !!!

      Delete
    4. Good Job, finally freaking worked.

      Delete
    5. my comand error on line "var colorCell = activeSheet.getRange(colorCellA1Notation);" is range not found Whats is solutions?

      Delete
  33. Hi just wondering if this script is able to be used within a sumif function?
    I tried =sumIF(G2:G6,"Apples",sumColoredCells(H2:H6,P2)) but its not working :(

    ReplyDelete
  34. Buen día

    me salta este error, alguien conoce como solucionarlo.

    TypeError: No se puede llamar al método "pop" de null. (línea 17, archivo "Código")

    ReplyDelete
    Replies
    1. Cambia la , por ; como está en estas líneas:
      var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();
      var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();

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

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

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

    ReplyDelete
  38. It is wonderful! You Really Saved Me.

    Is there any chance that this, too, can be changed to get also the "amount of font color"? Thank!
    this website

    ReplyDelete
    Replies
    1. Yes:

      /**
      * @param {range} sumRange Range to be evaluated
      * @param {range} colorRef Cell with background color to be searched for in sumRange
      * @return {number}
      * @customfunction
      */

      function sumColoredCells(sumRange,colorRef) {
      var activeRange = SpreadsheetApp.getActiveRange();
      var activeSheet = activeRange.getSheet();
      var formula = activeRange.getFormula().toString();
      formula = formula.replace(new RegExp(';','g'),',');


      var rangeA1Notation = formula.match(/\((.*)\,/).pop();
      var range = activeSheet.getRange(rangeA1Notation);
      var bg = range.getFontColors();
      var values = range.getValues();

      var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
      var colorCell = activeSheet.getRange(colorCellA1Notation);
      var color = colorCell.getFontColor();

      var total = 0;

      for(var i=0;i<bg.length;i++)
      for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
      total=total+(values[i][j]*1);
      return total;
      };

      Delete
    2. It is not working in my sheets. It says unknown filter. Can someone please help me?

      Delete
  39. Hi there, I am having the issue where when inputting the formula I am getting the error "Result was not a number". I have the inputted as "=sumColoredCells(A2:A193,A11)". Any help would be appreciated. Thanks!

    ReplyDelete
    Replies
    1. You need to move your output box. Your range includes the cell you are trying to put your count/sum in. Move it to a cell out of your range.

      Delete
  40. I keep getting an error:
    TypeError: Cannot call method "getSheet" of null. (line 9, file "sumbycolor")

    sumbycolor is just the name of the function I chose to use.

    ReplyDelete
    Replies
    1. the same situation. the same error

      Delete
    2. solved. chenged , to ; FND do not forget to change in the formula =sumColoredCells(A1:C6,E1) also , TO ; :)

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

    ReplyDelete
  42. Hi everyone. I use this sumColoredCells function but it will NOT auto refresh/calculate if you change cell colors. The only way it refreshes is to change a value in the range. A work-around I found was to create a checkbox in unused cell somewhere to add 1 (or use insignificant fractional (.000001) if you are rounding) to a number in the range, and unchecking will add 0. This will act as a refresh "toggle" switch. Not really ideal, I know... but this is for my personal use and only a minor hassle.

    ReplyDelete
  43. is there a way to only count the colored cells if they follow a certain text in the column left to it?

    Such as column A being a list of vendor and Column B being a status of invoice (highlighted by color to signify status)

    If I wanted to find the total amount of unpaid invoices (highlighted in RED) that Vendor B has for instance. So therefore only summing the red amounts associated with Vendor B in column two and ignoring red cells from other vendors.

    I imagine a VLookup type formula added to this, but am not practiced enough to figure it out.

    ReplyDelete
  44. If there are dates in the selected range, how do we update the script to avoid sum the dates???

    ReplyDelete
  45. I am looking for a way to change the background color of a cell based on the background color of another cell in GOOGLE SHEETS. I have used conditional formatting to color cells in 'column A' based on text found in a cell but then I would like the neighboring cell in 'column B' to match the color found in the neighboring column A cell. I will then use the 'sum by color' as found in this video to add the colored cells in 'column B'. The video is exactly what I was looking for and it works flawlessly, just need to figure out how to color a cell based on another cell to use this to its full potential. Thank you!

    ReplyDelete
  46. Você tem um script para contar celulas coloridas ao inves de somá-las? Obrigada!

    ReplyDelete
    Replies
    1. Consegui aqui!!


      /**
      * @param {range} sumRange Range to be evaluated
      * @param {range} colorRef Cell with background color to be searched for in sumRange
      * @return {number}
      * @customfunction
      */

      function contarCelulasColoridas(contRange,colorRef) {
      var activeRange = SpreadsheetApp.getActiveRange();
      var activeSheet = activeRange.getSheet();
      var formula = activeRange.getFormula().toString();
      formula = formula.replace(new RegExp(';','g'),',');


      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 total = 0;

      for(var i=0;i<bg.length;i++)
      for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
      total=total+1;
      return total;
      };

      Delete
  47. hello, I have the formula exactly the same as shown but it is not allowing me select a function to run in script editor? The function is on the script, but not being detected by GS. How do I enter this in for the formular?

    ReplyDelete
  48. Not working for me here the sheet link can someone please solve the problem.

    ReplyDelete
  49. This script work if the cell with format to sum isn't the same of the formula... it's correct?

    ReplyDelete
  50. This script work if the cell with format to sum isn't the same of the formula... it's correct?

    ReplyDelete
  51. worked so good!!! THANK YOU FOR THIS! :)

    ReplyDelete
  52. Thank you! It really worked for me!

    ReplyDelete
  53. Below code has worked for me.

    /**
    * @param {range} sumRange Range to be evaluated
    * @param {range} colorRef Cell with background color to be searched for in sumRange
    * @return {number}
    * @customfunction
    */

    function sumColoredCells(sumRange,colorRef) {
    var activeRange = SpreadsheetApp.getActiveRange();
    var activeSheet = activeRange.getSheet();
    var formula = activeRange.getFormula().toString();
    formula = formula.replace(new RegExp(';','g'),',');

    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 total = 0;

    for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
    if( bg[i][j] == color )
    total=total+(values[i][j]*1);
    return total;
    };

    ReplyDelete
    Replies
    1. Your comment has been very helpful. This worked for me too. Thank you so much!

      Delete
  54. I also have "TypeError: Cannot read property 'pop' of null"

    I tried replacing "," by ";" and I tried the scripts suggested in the comments above,but nothing works. Anyone has the same issue and was able to fix it? Thanks a lot!

    ReplyDelete
    Replies
    1. ciao, hai poi risolto con questa funzione? io ancora ho difficoltà ad usarla

      Delete
  55. ciao, ho bisogno di usare questa funzione, ma non mi funziona
    dopo aver eseguito, compare questo messaggio: TypeError: Cannot read property 'pop' of null
    sumColoredCells @ myfunction.gs:14
    qualcuno potrebbe scrivere il codice corretto per poter usare questa benedetta funzione (sommaCelleColorate)

    ReplyDelete
  56. I am getting the error "Result was not a number" , please help

    ReplyDelete
  57. Is it possible to add another column lookup to this?
    Use Case #1.
    I have values in a column that I want to group and sum but only if a specific color. In column A would be "Type" Plumbing, Electrical, Floors, etc and column B would be Cost and it would result field(s) would consider the color and the value. If cost is green and "target type" is Plumbing. I'd have multiple columns for reference and a different sumColoredCells reference for each.

    ReplyDelete
  58. Hi, it works, but you need some changes to the code.

    function sumColoredCells (sumRange, colorRef) {

    these "sumRange" and "colorRef" parameters are not used in the code, so they must be changed to "rangeA1Notation" and "colorCellA1Notation".
    as follows:
    function sumColoredCells (rangeA1Notation, colorCellA1Notation) {

    ReplyDelete
    Replies
    1. Or just simply delet it...
      function sumColoredCells () {

      Delete
    2. The code works! Thanks.

      Has anyone figured out how to update the code to make the cell auto update the sum when any of the values changes?

      Thanks

      Delete
  59. I would also appreciate a workaround with an autorefresh.
    Since often the numbers do not change, but the colors do, for what I need it, so, it is a bit annoying that we need to retype values all the time, making it prone to mistakes as well. Any thoughts? My skills in coding this thing are null :)

    ReplyDelete
  60. This code is returning the error TyperError: Cannot read property 'getSheet' of null

    ReplyDelete