How to Sum Colored Cells in Google Sheets (2 Ways)

A common question in the spreadsheet space is: How do you sum colored cells in Google Sheets?

This question arises since it is a common work-related requirement and also since there are no direct ways to do this. Meaning that there are no built-in functions or features to sum colored cells.

So, as users, we have to get creative and take advantage of Google Sheets’ helpful features like Apps Script and Add-ons to get the job done.

But then some users might ask: Can you sum colored cells using functions like COUNTIF or SUMIF?

This is a valid question, considering the functions take a condition to count or sum cells respectively. Unfortunately, there’s still no way to input cell background color as a condition in a built-in Google Sheets formula.

Thus, we have to stick to a more advanced approach, like those we will see in this article.

To instead see how to count colored cells, see this article: Count Cells with Color in Google Sheets (3 Easy Ways)

2 Ways to Sum Colored Cells in Google Sheets

For our examples, we will use the following worksheet:

Here, we have different Sales numbers for different Regions.

The scenario is that conditional formatting can be used to color entries depending on a condition, which in this case is New York.

What we want to do is sum all the Sales values of the cells colored by conditional formatting.

This is a common scenario that you might find working with many spreadsheets.

Note: An alternative to this would be to sum by the region values themselves with the help of VLOOKUP or INDEX-MATCH.

1. Create a New Function with Apps Script to Sum Colored Cells

While there are some amazing built-in functions in Google Sheets, it can’t always account for the depth of human requirements. And performing a sum with colored cells in Google Sheets is one such scenario.

But Google Sheets does allow its users to create their own functions according to these special requirements with the help of the Apps Script feature.

Let’s go through this process step by step:

Step 1: Navigate to the Apps Script option from the Extensions tab.

Extensions > Apps Script

Step 2: Enter the following code to sum all colored cells in Google Sheets:

function SumColorCells(sumRange,colorRef) {
  var activeRng = SpreadsheetApp.getActiveRange();
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRng.getFormula();

  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var bgs = activeSheet.getRange(countRangeAddress).getBackgrounds();
  var sumValues = activeSheet.getRange(countRangeAddress).getValues();  
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var backGround = activeSheet.getRange(colorRefAddress).getBackground();

  var totalValue = 0;
  for (var i = 0; i < bgs.length; i++)
    for (var k = 0; k < bgs[i].length; k++)
      if ( bgs[i][k] == backGround )
        if ((typeof sumValues[i][k]) == 'number')
          totalValue = totalValue + (sumValues[i][k]);
  return totalValue;
};

Make a note of the function name, which in this case is SumColorCells. We will use this to call the function in our spreadsheet.

Step 3: Save the code using the Save icon up top. This may take a few seconds.

Click on the Run icon to activate the code in the current active worksheet.

At this point, Apps Script may ask for access permission. Allow it to access the spreadsheet.

Step 4: Reload the spreadsheet. Then call the function with parameters to get the result:

Note: Don’t worry if a red underline appears when inputting the function, it will work just fine. And since it’s an external function, it may take a few extra seconds to process.

How does the SumColorCells function work?

The syntax for the function is simple as it takes only two arguments:

The data_range represents the range of cells that will be used in the sum. In our case, it was the data in the Sales column.

The background_color_reference is the most important argument here. From this, we get the cell reference of the cell whose background color will be used as the condition.

In our example, we have referred to cell E1 whose background color matches the corresponding Sales values of the Region. Both colors must match for a TRUE statement.

Here is another example with a different color:

2. Install an Add-On to Sum Colored Cells

Not everyone can write and use scripts reliably, and that is absolutely OK!

For those users, Google Sheets allows the usage of free add-ons to cover complex spreadsheet requirements that many users have in simple clicks.

The add-on in question that can help sum up colored cells is called Function by Color.

Note: Function by Color is one of the features of the much larger add-on Power Tools in Google Sheets. Since this article works with a particular process, we are only using Function by Color.

Here’s the step-by-step process of adding and using the add-on in Google Sheets.

Step 1: Navigate to the Get add-ons option from the Extensions tab of Google Sheets.

Extensions > Add-ons > Get add-ons

Step 2: in the Marketplace window, search for the addon “function by color”.

From all the options available, select the one with the highest download. Refer to the image below:

Step 3: Once on the addon page, Install it to add it to your Google Sheets application.

Note: The add-on may ask for permission before installing, allow it.

The add-on should now appear in the spreadsheet under the Extensions tab.

If it doesn’t, simply reload the spreadsheet.

Step 4: With the add-on installed, all we have to do now is Start it.

Extensions > Function by Color > Start

Give it a few seconds and the Function by Color window will appear:

Step 5: Set the following conditions:

1. Select the range of cells from which the data for the sum will be collected. You can use the cell mesh icon to easily input the range.
2. Use the eye-dropper tool on the desired background color. Important: Use the eye-dropper tool on one of the cells of the Select range section.
3. Set the function choice. In this case. It will obviously be SUM.
4. Set the cell where the result will appear. In this case, it is cell E2.

Step 6: Click on the Insert function button to see the result.

As you can see, the add-on generates a formula that automatically caters to the set conditions.

Final Words

That concludes our simple guide on how to sum colored cells in Google Sheets.

Even though we have no built-in features to directly solve this, Google Sheets does provide excellent assistance to users in the form of Apps Script and Add-ons.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

• How to Add Up A Column in Google Sheets
• How to Perform Conditional Sum in Google Sheets (Easy Guide)
• Applying SUMIF from Another Sheet in Google Sheets
• How to Count Cells in Google Sheets (4 Easy Ways)
• COUNTIF Multiple Criteria in Google Sheets (3 Ways)