Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

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 / Sum cell values based on background, font, conditional formatting color in Excel:

Kutools for Excel's Count by Color feature can help you to get some calculations based on font, background or conditional formatting color as you need in Excel worksheet.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


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 60 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 Enterprise > 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!



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Kevin · 1 months 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.
    Francisco · 1 months ago
    Thank you so much, truly useful!
  • To post as a guest, your comment is unpublished.
    Roberto · 1 months 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 backGrounds = SpreadsheetApp.getActiveSheet().getRange(countRange).getBackgrounds();
    var backGround = SpreadsheetApp.getActiveSheet().getRange(colorRef).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;
    return countCells;
    }

    function onEdit(e)
    {
    SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
    }
  • To post as a guest, your comment is unpublished.
    Marco · 5 months 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.
    Adam · 5 months ago
    This script used to work great, but it doesn't work anymore. Is there a change that brought it down?