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

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

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:

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

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

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

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

2. Is my script correct?

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

4. This comment has been removed by the author.

5. Works great... Thank you

6. 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!

7. This comment has been removed by the author.

8. I am getting a Circular dependency error! Help!

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

E1 is the reference color.

9. It says ERROR. Can someone help me?

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

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

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

11. 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.......

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

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?

13. 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?

14. 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

1. Thanks! It has solved the issue.

2. This comment has been removed by the author.

3. This comment has been removed by the author.

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

16. How do you make the cells update automatically?

17. 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();﻿

18. This comment has been removed by the author.

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

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

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

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

21. 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

22. This comment has been removed by the author.

23. 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: