Google Sheets – Sum or count values based on cell color

Overview

If you’re using Google sheets and you have a list of amounts that you want to sum or count based on the background color of the cells, there’s no built-in function to do it. However, there are a relatively easy set of steps to make your own functions to get it done. You’ll be able to count based on cell color and you’ll be able to sum as well.

**This technique has been updated. There is a new YouTube video showing an easier way with this Sheet**.

Custom formulas in action

Count by cell color

If you look in the live spreadsheet, you will see the custom formulas being used for summing and counting by cell color. The counting is done by a formula with the nice little name of countColoredCells which takes the range and cell with the background color that you want as its two inputs. You want to count B4 through B9 and you want to count the number of cells that have the background color used in cell B8, which is the light blue cell with the dotted line around it.

If you change the second parameter above from B8, a light blue cell, to B5, a yellow cell, the result would be 3 because there’s three yellow cells.

Sum by cell color

sumColoredCells is returning a 24 which is the sum of all the light blue cells. It is similar to the count function – it is summing the colored cells in B4 through B9 that are formatted like B8.

Creating custom formulas

How do we make these functions and any other custom function that you’re so inclined to write? First, go to Tools and you go to Script editor... Don’t worry, we’re just going to copy and paste code below. The code for countColoredCells was obtained from this page at iGoogleDrive.

`````` /**
* @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 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;
};
``````

And this code for sumColoredCells was obtained from the same blog, but a different page.

`````` /**
* @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);
};
``````

Script editor

After you go to Tools then Script editor, you come up with a blank screen. But if you don’t, just do a new script file. Paste the code into the blank window. Repeat for each code section above and name them countColoredCells and sumColoredCells. For each file, the script editor puts the “.gs” at the end of the file name which indicates that it is a Google Script. After you make these two, save them, come back to your spreadsheet, type in the formulas, and it should work for you. See the video and live spreadsheet for further clarification.

Watch the video

Live examples in Sheets

Go to this spreadsheet for examples of counting cells by cell color that you can study and use anywhere you would like.

Using a plugin

As an alternative to the technique above, you may want a plugin to do the heavy lifting for you. I like to use a plugin called Power Tools. This will give you a menu option with, among other things, the ability to run functions by color.

Disclosure: This is an independently owned website that sometimes receives compensation from the company's mentioned products. Prolific Oaktree tests each product, and any opinions expressed here are our own.

0 thoughts on “Google Sheets – Sum or count values based on cell color”

• xxsays:

Look at the pinned comment on the linked video on YouTube. There are replies too. The video is here https://youtu.be/TMbuwLD4Dgw and the content of the first comment is:

if you have an error with sumColoredCells() “Range not found (line 21, file “Code”)” replace line 20’s
var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
with:
var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
// remove the garbage, error making space at the start of only the first value
in the range
colorCellA1Notation = colorCellA1Notation.replace(/\s/g, “”);

I don’t know why it was doing that to me but it was

• Nicksays:

Calling trim() fixed this error me:

Replaced:

var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();

with:

var colorCellA1Notation = formula.match(/\,(.*)\)/).pop().trim();

• Bruce Morgansays:

Everything works as expected but I’m confused by changes and triggers. I am hoping that this summation will recalculate when a change is made to the sheet. It does recalculate when I change a value but not when I change the background color. I have triggers set for on change and on edit but they don’t seem to work for changing background colors. Any suggestions? I am guessing that this would work with a timed trigger but I need the recalculation to occur in real time as the background changes.

Thanks for your efforts on this!

• Prolific Oaktreesays:

I don’t know, but my suggestion would be to post the same question to the video on YouTube. There is more traffic and comments there so you will have a better chance of getting a prompt answer. Here’s the link.

https://youtu.be/TMbuwLD4Dgw

• Mattsays:

I tried this and it works, except if you do multiple ranges it won’t work. Is there a way to add multiple ranges such as this =sumcoloredcells(A10:A25,A35:A38:A46:A48,A10) any help would be appreciated. It’s for my budget sheet I’m trying to organize it better.

• Mattsays:

I messed up the function lol its =sumcoloredcells(A10:A25,A35:A38,A46:A48,A10)

• tvraaosays:

whether sum total gets updated whenever there occur changes in cell colour in the range ?

• Prolific Oaktreesays:

Here’s a video with a linked Sheet of a new version that refreshes when you check a box.

• Gayasays:

Hi, can you share the video link please. Mine also does not refresh when the color changes.

• Ryansays:

Hey, Script Editor says there is on function to run in the functions box next to the save button in the tool bar.. Doesn’t work on my sheets. Help?

• Prolific Oaktreesays:

Not sure except that you need to make sure you have full permissions to the file.

• Michaelsays:

I get a #NAME error message and a red line under “countColoredCells” I assume sheets does not recognize my script, but why?

I have used your code for count and sum colored cells with great success, but now it seems like I can’t make it work. I came to your page and copy and pasted the code back into my screen, but it still won’t work. Are you able to help me with this? I have a new Macbook Air running Google Sheets in Chrome. Below is what is happening when I run it:
Execution log
4:57:58 PM Notice Execution started
4:58:33 PM Error TypeError: Cannot read property ‘pop’ of null
countColoredCells @ Code.gs:13

–The top line below is line 13 which is where the above “Code.gs:13” takes me when I click on it (on my computer, that phrase is a link)
var rangeA1Notation = formula.match(/\((.*)\,/).pop();
var range = activeSheet.getRange(rangeA1Notation);
var bg = range.getBackgrounds();
var values = range.getValues();

• Prolific Oaktreesays:

It’s hard to tell without seeing that sheet, but my guess would be that there’s something in the range at the formula doesn’t like such as an empty cell.

• Kaikeastsays:

I am having similar trouble, it says:

9:04:39 AM Error
TypeError: Cannot read property ‘pop’ of null
countColoredCells @ countcolour.gs:13

• Raugsays:

countColoredCells
————
“for(var i=0;i<bg.length;i++)
for(var j=0;j 0)
count=count+1;
return count;”
Count only colored cells with positive content.
———–
“var values = range.getValues();”
This line is only necessary in this kind of case.