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!


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
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.
    Marco · 3 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 · 3 months 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.
    Steve · 4 months ago
    Internet is full of instances of this solution and it doesn't work.
  • To post as a guest, your comment is unpublished.
    Marco · 4 months 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.
    desi · 4 months ago
    thank you :*