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

In daily spreadsheet work, you may encounter situations where you need to count or sum cell values according to the specific cell background color, as illustrated in the screenshot below. For instance, you might want to tally or total only those cells highlighted in a particular color to quickly analyze data by category or status. This guide will address how to accomplish this not only in Google Sheets, which lacks built-in support for such color-based calculations, but also in Microsoft Excel, which provides several varying approaches, from built-in features to advanced utilities.

Understanding how to handle color-based data analysis can make your work more efficient, especially when colors are used to mark statuses, priorities, or categories. We’ll also discuss different solutions, compare their usage scenarios, and provide practical operation tips as well as error reminders to ensure your tasks proceed smoothly.

• 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

Google Sheets does not offer a direct option to count cells based on background color. However, you can achieve this with a custom Apps Script. This script acts as a user-defined function, allowing you to reference it like a formula. Here’s how to set up and use the script:

1. Click Tools > Script editor to access the scripting environment. See screenshot:

2. In the project window, select File > New > Script file to open a new code module, as shown:

3. When prompted, enter a name for your new script code and confirm. Name the script meaningfully to help identify its purpose later.

4. Click OK, then copy and paste the following code to replace any sample code in the module. Ensure you paste it exactly as it is provided.

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. Save this script file, return to your sheet, and use the new function just like any Google Sheets formula. Enter: =countcoloredcells(A1:E11,A1) in a blank cell to count cells in the range A1:E11 matching the color of A1. Press Enter to obtain the result. If prompted for permissions, authorize the script to run in your sheet.

Note: A1:E11 is your data range; A1 is the reference cell of the color to count. Make sure reference cells have the exact color and avoid merged cells for best reliability.

6. To count other colors, repeat the formula with a different color reference cell as needed. If your range changes, adjust the range in the formula accordingly.

If you receive an error or an unexpected result, double-check that the script was saved, and that you used the correct color reference. Apps Script-based functions only recalculate if the function or its arguments change—if you later recolor your cells, re-enter the formula or press Enter again to refresh.

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

Summing cell values based on a defined cell color in Google Sheets requires a similar approach using an Apps Script. This is especially useful for financial sheets, status logs, or any scenario where colors represent categories with numeric data underneath.

1. In Google Sheets, open the Script editor via Tools > Script editor. When in the project window, select File > New > Script file to add a new code module. Assign a unique name in the prompt to help track its purpose, such as "SumColoredCells." Confirm to create the module.

2. Click OK, and in the new code module window, replace any default code by pasting in the provided script for summing colored cells. Carefully ensure all code is copied, as missing characters can result in syntax errors.

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. After saving the script, return to your sheet and enter the formula =sumcoloredcells(A1:E11,A1) in a blank cell, then press Enter. This formula sums values in A1:E11 where the background color matches A1. When using this function, make sure all target sum cells are numeric; non-numeric values will be ignored.

Note: A1:E11 represents your data range, and A1 provides the color reference. The formula will only sum visible numeric values—ensure that merged cells or errors within the range do not affect your totals.

4. You can replicate the above process to sum values for different color categories by changing the color reference cell in your formula. If your data is updated or you change background colors, remember to refresh the formula for updated output.

If the sum returns zero or an error value, verify that the range contains numbers and the color match is exact. Also, the recalculation is not automatic if only the cell color changes—edit the formula cell to force an update.

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

When working in Microsoft Excel, counting or summing cells by color is a frequent requirement, especially within project management, inventory, or quality control reports. Kutools for Excel offers a dedicated Count by Color utility, which enables you to obtain counts and totals by background or font color directly — this is particularly helpful for larger data ranges and when you need quick, repeatable results.

Once Kutools for Excel is installed, proceed with these steps:

1. Highlight the range where you wish to count or sum by color, then click Kutools Plus > Count by Color. Refer to the screenshot below for guidance:

2. The Count by Color dialog box appears. Set Standard formatting under Color method, and choose Background for Count type. Check the preview and options carefully:

3. Click Generate report to create a new worksheet containing a breakdown of counts and sums for each color present in your range. This report includes both the number and the sum of colored cells, allowing for easy reference or further analysis.

Note: This feature can also calculate values based on conditional formatting or font color. Use conditional formatting rules for dynamic analyses; otherwise, the tool works best for static color fills. Any changes in source cell colors would require rerunning the Count by Color utility for updated results. If you experience issues, confirm that Kutools is active and up to date.

Click Download and free trial Kutools for Excel Now!