Skip to main content

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!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (79)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Hey I just wanted to thank you for this guide. It totally worked! Awesome! 👍
This comment was minimized by the moderator on the site
Hii, i need help!
Did somebody have the right script?
if u have it, can u pls send me?
thanks
This comment was minimized by the moderator on the site
i need the auto update for the sumcoloredcells formula
do you know how can i have it?
This comment was minimized by the moderator on the site
questo funziona

// restituisce la somma dei valori in un range in base al colore di sfondo della cella
// uso: =SommaSeSfondo(range;cella colore di riferimento )
// =SommaSeSfondo(B1:B8;E1)

function SommaSeSfondo(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;
};
This comment was minimized by the moderator on the site
Hi Samal,

You have to use the formula:

=SommeCouleurs(A:A;A1) => A:A : columns that contain the colors to count and A1 : The cell that contains the example of the color.
This comment was minimized by the moderator on the site
'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?
This comment was minimized by the moderator on the site
'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?
This comment was minimized by the moderator on the site
'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?
This comment was minimized by the moderator on the site
Bonjour a tous,

J'utilise Google Sheet en version Française et voici le script le plus fonctionnel que j'ai trouvé:

function SommeCouleurs(plage,couleur) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formule = activeRange.getFormula();


var laplage = formule.match(/\((.*)\;/).pop();
var range = activeSheet.getRange(laplage);
var bg = range.getBackgrounds();
var values = range.getValues();

var lacouleur = formule.match(/\;(.*)\)/).pop();
var colorCell = activeSheet.getRange(lacouleur);
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);
return total;

};


function CompteCouleurs(plage,couleur) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formule = activeRange.getFormula();


var laplage = formule.match(/\((.*)\;/).pop();
var range = activeSheet.getRange(laplage);
var bg = range.getBackgrounds();
var values = range.getValues();

var lacouleur = formule.match(/\;(.*)\)/).pop();
var colorCell = activeSheet.getRange(lacouleur);
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;
};

Formule a utiliser:

=CompteCouleurs(A2:A;A1)

Il s'actualise automatiquement.
En espérant que ca vous aidera
This comment was minimized by the moderator on the site
Добрый день!
Проверил, автоматически не пересчитывает, если изменить цвет в диапазоне ((
This comment was minimized by the moderator on the site
非常感謝大大分享~很實用我是用Google 試算表編輯
請問程式碼能教怎麼加總指定的文字顏色的數字總和嗎? (把 儲存格顏色 改為 文字顏色) 拜託~~
This comment was minimized by the moderator on the site
Hi, I have set it in google sheets as You have described, but it showed me error "Type error: Cannot read property 'pop' of null". PLS can you help me?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations