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

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 activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  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 activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var sumValues = activeSht.getRange(countRangeAddress).getValues();  
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  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]);
  return totalValue;
};

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.

Kutools for Excel : 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.

Click Download and free trial Kutools for Excel Now!


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.
kte tab 201905

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!
officetab bottom
Comments (67)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi! Great script but I run into a problem when trying to edit the values within the coloured cells. The script does not refresh the values if I change the colour of the cell. I can only force it to refresh if i delete the contents of the cell, change the colour and then enter the value again.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Thanks for the help with "Count cells based on color for google sheets". Limitation seems to be that when cell colors are changed the sheet does not refresh and totals are incorrect. I have to delete the code in the total cell and copy again from neighbour cell. Not ideal, but it works. Thanks again.
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
Your sumColoredCells() function is incorrect as it doesn't use sumRange
This comment was minimized by the moderator on the site
Hello, Dave,
The above script works well in my Google sheet.
When applying the formula, you should save the script window first.
Please try it, Thank you!
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Hi, Laura,
The script works well in my google sheets.
If there is problem in your google sheet, I can share my google sheet with you.
Please give your email address.
Thank you!
This comment was minimized by the moderator on the site
Hello, I have the same problem, have you fix it?
This comment was minimized by the moderator on the site
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

var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();

var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();
This comment was minimized by the moderator on the site
cambio la coma (,) por punto y coma (;) pero aun así no me cuenta el color. me sale como resultado (0 cero).
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
I'm getting "Action Not Allowed Line 0" What am I doing wrong?
This comment was minimized by the moderator on the site
I keep getting an error "Action not allowed (Line 0)". What does this mean?
This comment was minimized by the moderator on the site
does it auto update when new data is added?
This comment was minimized by the moderator on the site
Does not seem like it
This comment was minimized by the moderator on the site
Did you ever figure out how to get this to auto update?
This comment was minimized by the moderator on the site
Did anyone find a way to auto update?
This comment was minimized by the moderator on the site
I need to figure out how to get it to auto update too!... Anyone find the answer?
This comment was minimized by the moderator on the site
Did anyone find a way to auto update?
This comment was minimized by the moderator on the site
No, and I'm trying to find the answer
This comment was minimized by the moderator on the site
Replace this lines:
var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();


var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();

Notice than , was changed for ;
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
im trying to do something like this
=countColoredCells (H62:H,B2)countif("MBA13R15")
This comment was minimized by the moderator on the site
Hello,
The formula does not work for me.
If I do this in spreadsheet only errors will occur.
This comment was minimized by the moderator on the site
Thanks, this worked great!
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
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 gives me an #Error - Range not found (line 6).


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


Thanks in advance.
This comment was minimized by the moderator on the site
thank you :*
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
Internet is full of instances of this solution and it doesn't work.
This comment was minimized by the moderator on the site
This script used to work great, but it doesn't work anymore. Is there a change that brought it down?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations