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();
  
  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

11 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
  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
  8. It's giving an error, can someone help me with this?

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

    ReplyDelete