Note: The other languages of the website are Google-translated. Back to English
or

Register

or

How to count or sum cells based on cell color in Google sheet?

Counting or summing cell values based on specific cell background color to get the result as following screenshot shown. This article, I will talk about how to solve this task in Google sheet and Microsoft Excel.

Count cell values based on cell color with script in Google sheet

Sum cell values based on cell color with script in Google sheet

Count or sum cell values on cell color with Kutools for Excel in Microsoft Excel

Count cell values based on cell color with script in Google sheet

The following script can help you to count the cell values based on specific cell color, please do as this:

1. Click Tools > Script editor, see screenshot:

2. In the opened project window, click File > New > Script file to open a code window, see screenshot:

3. And in the prompt box, please enter a name for this script code, see screenshot:

4. Click OK and then copy and paste the following code to replace the original code into the code module, see screenshot:

```function countColoredCells(countRange,colorRef) {
var activeformula = activeRg.getFormula();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
```

5. Then save this script code, and go back the sheet, enter this formula: =countcoloredcells(A1:E11,A1) into a blank cell, and then press Enter key to get the calculated result. See screenshot:

Note: In this formula: A1:E11 is the data range that you want to use, A1 is the cell filled with specific color that you want to count.

6. Repeat the above formula to count other specific colored cells.

Sum cell values based on cell color with script in Google sheet

To sum the cell values with a specific cell color, please apply below script code.

1. Click Tools > Script editor to go the project window, and click File > New > Script file to insert another new code module, then, in the prompt box, please type a name for this script, see screenshot:

2. Click OK and in the opened code module, copy and paste below script code to replace the original code, see screenshot:

```function sumColoredCells(sumRange,colorRef) {
var activeformula = activeRg.getFormula();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
};
```

3. And then save this code, return to the sheet, and enter this formula: =sumcoloredcells(A1:E11,A1) into a blank cell, and press Enter key to get the calculated result, see screenshot:

Note: In this formula: A1:E11 is the data range that you want to use, A1 is the cell with a specific background color that you want to sum.

4. And then you can repeat the above formula to sum other specific colored cells.

Count or sum cell values on cell color with Kutools for Excel in Microsoft Excel

In Microsoft Excel, to count or sum the cell values based on specific cell color, Kutools for Excel’s Count by Color utility can help you to finish this task as quickly as you can.

 : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

After installing Kutools for Excel, please do as this:

1. Select the cells to range that you want to count or sum based on cell color, and then click Kutools Plus > Count by Color, see screenshot:

2. In the Count by Color dialog box, choose Standard formatting from the Color method drop down list, and then select Background from the Count type drop down, see screenshot:

3. Then click Generate report button, and new worksheet with the calculated results is generated at once, see screenshot:

Note: With this powerful feature, you can also calculate the cell values based on conditional formatting or font color.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
or post as a guest, but your post won't be published automatically.
• To post as a guest, your comment is unpublished.
· 1 months ago
thanks, :)
• To post as a guest, your comment is unpublished.
· 3 months ago
Autoupdate of the formula

A tip is to create a script that inserts a row in the range and then removes the same row again. And then set up a time-based trigger for the function =)
• To post as a guest, your comment is unpublished.
· 3 months ago
it errors for me :/
• To post as a guest, your comment is unpublished.
· 3 months ago
@Andrew I have the same proble. Am I missing anything?
• To post as a guest, your comment is unpublished.
· 8 months ago
Thank you so much for these scripts, Truly appreciated, it's not to get something that works out of the box.

Question how would I go about modifying the values sum script to pull part of a cell, for exammple a cell that contains the following:
909 & 1909
{1 of 2}
SM & M (3.5)

How would I go about pulling the "3.5" in parenthesis and sum that numerical value coming out multiple cells of the same color. I know how to pull the value out in google sheets but can't figure out how to combine it with the cell color script.

Thank you.

• To post as a guest, your comment is unpublished.
· 10 months ago
Has anyone updated this for the Sum colored cells? i'm ok with copying and pasting the forumla for now but it would be nice if i highlighted more cells, it would add those cells to the total automatically. I'm a newb when it comes to reading code, i tried a few things based on all the comments but alas, i'm still new.

Thanks,

• To post as a guest, your comment is unpublished.
· 1 years ago
PLEASE SOMEONE COULD HELP ME ?
I WAS LOOKING FOR HOW TO SOLVE THAT A MONTH AT LEAST. I TRIED MANY THINGS, CHANGED COMMAS .... AND NOTHING SOLVE THAT...

------------- TypeError: Cannot call method "pop" of null. (line 5, file "SumByColor") -----------------

//*ORIGINAL - var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
//*I TRYING - var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();
//*ORIGINAL - var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
//*I TRYING - var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();

• To post as a guest, your comment is unpublished.
· 1 years ago
HI, Brian! Thank's for your effort to give us the updated code. The auto update feature doesn't works for me. It doesn't write a new random number in A1 automatically, s i have to do it manually to get refresh. Is it as planed or Do i miss something? Thanks for your reply.
• To post as a guest, your comment is unpublished.
· 1 years ago
hi Brian, I have tried to use the updated version. It is improved but still not always update after change color.
is it possible to add a command to refresh the 'A1' value timely. thanks.
• To post as a guest, your comment is unpublished.
· 1 years ago
@coolkev99 Hi Kevin,

Hope you are keeping well. I have been facing the same problem, Can you please share how to create checkbox to add 0000.1 or so. my email id is sudarshan1210@gmail.com. Looking forward to your help.
• To post as a guest, your comment is unpublished.
· 1 years ago
@Lenore Hello, Lenore,
The Brian's code is worked well to auto update the result when you change the cell color, please apply it.
Yes, you should pasting all of the above code to replace the original one.
• To post as a guest, your comment is unpublished.
· 1 years ago
@Brian Do we paste all of this to replace the original code?
• To post as a guest, your comment is unpublished.
· 1 years ago
Hi all, I wanted to give you an updated version of how to count the number of cells that are a given color. This version solves a couple of issues:

1. The count not updating when the doc changes and
2. Errors from adding another parameter to the function

// Unsed third argument
function countColoredCells(countRange,colorRef,unUsed) {
var activeformula = activeRg.getFormula();
// The regex matches the parentheses as an array, gets the arguments as a string,
// then splits the arguments on the comma into another array
var arrayOfArguments = activeformula.match(/\((.*)\)/).pop().trim().split(',');
// Get the first argument which is the range
// Get the second argument, which is the reference color
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};

// Writes a random number to A1
function onEdit(e)
{
}

Usage: =countColoredCells(B2:E22,A28,\$A\$1)
• To post as a guest, your comment is unpublished.
· 1 years ago
@Patodox cambio la coma (,) por punto y coma (;) pero aun así no me cuenta el color. me sale como resultado (0 cero).
• To post as a guest, your comment is unpublished.
· 1 years ago
@Sam No, and I'm trying to find the answer
• To post as a guest, your comment is unpublished.
· 1 years ago
@coolkev99 I have the same issue and don't know how to fix it
• To post as a guest, your comment is unpublished.
· 1 years ago
TypeError: Não é possível chamar o método "pop" de null. (linha 5, arquivo "sumbycolor")

How can I fix this error? Thank you
• To post as a guest, your comment is unpublished.
· 1 years ago
Hola! Muchas gracias por tu artículo, ha sido de mucha ayuda, he logrado corregir el tema del formato regional de google cambiando . por ;
Lo que no consigo hacer es que se actualice la formula cuando ingreso nuevos datos. ¿Sabes cómo hacer esto?
Gracias
• To post as a guest, your comment is unpublished.
· 1 years ago
Hi everyone. I use this sumColoredCells function but it will NOT auto refresh 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 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.
• To post as a guest, your comment is unpublished.
· 1 years ago
Thank you so much, truly useful!
• To post as a guest, your comment is unpublished.
· 1 years ago
There were some errors on the code, like the range not being used at all and that the calculation was only made when the parameters were changed. With this version the calculation will be updated when there is an update on the sheet and the range has to be passed as a string, also some dummy cell has to be used to save a random value, you can put the color of the text just like the background to make it invisible:

=countColoredCells("B3:B145","B1",\$A\$1)

function countColoredCells(countRange, colorRef, unUsed) {
var countCells = 0;
for (var i = 0; i < backGrounds.length; ++i)
for (var k = 0; k < backGrounds[i].length; ++k)
if ( backGrounds[i][k] == backGround )
++countCells;
return countCells;
}

function onEdit(e)
{
}
• To post as a guest, your comment is unpublished.
· 2 years ago
@Sarah Did anyone find a way to auto update?
• To post as a guest, your comment is unpublished.
· 2 years ago
@Tomasz B I need to figure out how to get it to auto update too!... Anyone find the answer?
• To post as a guest, your comment is unpublished.
· 2 years ago
A quick solution but not the best is to add a new parameter and never use it, something like this:

function sumColoredCells(sumRange,colorRef,test)

The test parameter is never used, this issue here is that the values only will be refresed if first of all you set the color and then change the value.
• To post as a guest, your comment is unpublished.
· 2 years ago
This script used to work great, but it doesn't work anymore. Is there a change that brought it down?
• To post as a guest, your comment is unpublished.
· 2 years ago
Internet is full of instances of this solution and it doesn't work.
• To post as a guest, your comment is unpublished.
· 2 years ago
Hi there,
on Google sheets, seems not to be working, I went on debug and for both processes, in row 5 seems to be a type error, the message is: impossible to recall the "pop2 method of null. Does it makes sense? Thanks a lot!
• To post as a guest, your comment is unpublished.
· 2 years ago
thank you :*
• To post as a guest, your comment is unpublished.
· 2 years ago
Hello,

This function works, however, when i try to sum 2 instances of it in the same cell as in (in my case):

=sumColoredCells(A103:AW103, C171) + sumColoredCells(A138:AW138, C171)

It doesn't work even if i use SUM() or ADD(). Any ideas?

• To post as a guest, your comment is unpublished.
· 2 years ago
@skyyang Thanks for the reply. I have done that, but would love to consolidate the formula into a single cell, as I have to update the ranges every week. Anyway, thanks for checking!
• To post as a guest, your comment is unpublished.
· 2 years ago
@Geoff Hello, Geoff,
May be there is no direct formula to solve your problem, but, you can sum the colored cells separately first, and then, sum the two calculated results as you need.
• To post as a guest, your comment is unpublished.
· 2 years ago
The SumColoredCells Script works great. Do you know if there is a way to display (in a single cell) the sum of 2 colors? For example, if I wanted the sum of both the Yellow and Green Cells to display in a single cell, is that possible? I would think the formula would be:
=SUM(sumcoloredcells(A1:E11,A1)),(sumcoloredcells(A1:E11,A5)) ....But that results in an ERROR. Any ideas?
• To post as a guest, your comment is unpublished.
· 2 years ago
Thanks, this worked great!
• To post as a guest, your comment is unpublished.
· 2 years ago
@Michael Riddering Did anyone find a way to auto update?
• To post as a guest, your comment is unpublished.
· 2 years ago
Hello,
The formula does not work for me.
If I do this in spreadsheet only errors will occur.
• To post as a guest, your comment is unpublished.
· 2 years ago
im trying to do something like this
=countColoredCells (H62:H,B2)countif("MBA13R15")
• To post as a guest, your comment is unpublished.
· 2 years ago
Hi there,

Can I need some help with a similar formula to SUM total of number keywords matching and reflecting the number of colors.

What I what to achieve it determine the number of laptops which is a keyword but recognise the Available based on color of the cells?
• To post as a guest, your comment is unpublished.
· 2 years ago
@Laura The problem maybe is your regional configuration, this script thinks you are separating parameters by ',', but it is not your case probably. In my case, I use ';', so I modified the script, changing the character in the lines

• To post as a guest, your comment is unpublished.
· 2 years ago
@Designedforx Did you ever figure out how to get this to auto update?
• To post as a guest, your comment is unpublished.
· 2 years ago
Replace this lines:

Notice than , was changed for ;
• To post as a guest, your comment is unpublished.
· 2 years ago
@Sam Does not seem like it
• To post as a guest, your comment is unpublished.
· 2 years ago
@Laura Hello, I have the same problem, have you fix it?
• To post as a guest, your comment is unpublished.
· 3 years ago
does it auto update when new data is added?
• To post as a guest, your comment is unpublished.
· 3 years ago
I keep getting an error "Action not allowed (Line 0)". What does this mean?
• To post as a guest, your comment is unpublished.
· 3 years ago
I'm getting "Action Not Allowed Line 0" What am I doing wrong?
• To post as a guest, your comment is unpublished.
· 3 years ago
Is there a way to include negative numbers in the mix and get the correct sum? Mine keeps adding all the numbers in the range, even if the number has a negative sign on it.
• To post as a guest, your comment is unpublished.
· 3 years ago
@Laura Hi, Laura,
The script works well in my google sheets.
Thank you!
• To post as a guest, your comment is unpublished.
· 3 years ago
@Dave Gorman Hello, Dave,
The above script works well in my Google sheet.
When applying the formula, you should save the script window first.
• To post as a guest, your comment is unpublished.
· 3 years ago
@Todd Hello, Todd,
Yes, as you said, you should retype the formula when the cell color changes.
May be there is no other good ways to solve this problem.
If any other has good methods, please comment here.
Thank you!
• To post as a guest, your comment is unpublished.
· 3 years ago
Hey, thanks for this script. I'm having a problem in the script for google sheets: TypeError: Cannot call method "pop" of null. (line 5, file "SumByColor"). Can you please help?, thanks