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

35 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'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
  9. 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
  10. 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
  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. 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
  13. Hi, nope, is not working, the "TypeError: Cannot call method "pop" of null" still happening

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

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

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

    ReplyDelete
  18. 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
  19. 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
  20. This comment has been removed by the author.

    ReplyDelete
  21. 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
  22. I would sum colored cells, but also that have near a cell == "*"
    so I would add another range as parameter, containing the cells with "*"... but I don't understand how modify the code..

    Could you help me please?
    thank you

    ReplyDelete
  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