3 Ways to Sum Colored Cells in Google Sheets

Looking for a simple way to sum colored cells in Google Sheets? Well, you’re in luck! Google Sheets provides three powerful methods to help you accomplish this task. In this article, we will explore how to sum colored cells using the SUBTOTAL function, app script function, and add-ons. By the end, you will be able to streamline your workflow and save time. So without further ado, let’s dive right in.

How to Sum Colored Cells Using the SUBTOTAL Function

The SUBTOTAL function is a game changer when it comes to summing colored cells with the help of the Filter feature. Unlike other functions in Google Sheets, SUBTOTAL performs operations only on visible cells on the screen. This means that hidden or filtered cells will be excluded from the calculation. Let’s take a detailed look at this feature.

The syntax for the SUBTOTAL function is as follows:

=SUBTOTAL(function_num, range1, [range2], ...)

• function_num represents the function to be used. For example, SUM is represented by 9. If you want to exclude hidden cells from the calculation, add 100 to each number so that it falls between 101 and 111. For example, 109 represents the sum excluding filtered cells.
• range1 is the range on which the calculation is to be performed.
• range2 is an optional argument, similar to range1.

Now, let’s say you have a dataset and you want to sum all the colored cells within it. Use the following SUBTOTAL function:

=SUBTOTAL(109, D2:D11)

The first parameter (109) specifies the SUM function, while the second parameter specifies the range (D2:D11) on which the function should be applied.

To make things interesting, let’s go to the Data tab, select “Create a filter” from the dropdown menu, and apply a filter by color. All cells except for the selected color will be removed, and the SUBTOTAL function will display the sum of the colored cells. Isn’t that cool? Give it a try!

How to Sum Colored Cells Using Add-Ons

Now, let’s explore how to use add-ons in Google Sheets to sum colored cells. Although this method may seem a bit longer at first, once you get the hang of it, it will only take seconds to complete.

To begin, you need to enable add-ons and add Ablebits Power Tools. Here’s how you do it:

1. Go to the Extensions tab and click Add-ons from the dropdown.
2. Select Get Add-ons from the options.

A Google Workspace Marketplace prompt will appear on your screen. In the search bar, type “Power Tools” and select the first option that appears. Install Power Tools, and it will be ready to use in no time.

Now, let me walk you through the process:

1. Click Continue on the prompt to install Power Tools.
2. After installation, the Power Tools dialog box will appear on the right side of your screen.
3. If it doesn’t appear automatically, go to the Extensions tab, select Power Tools, and click Start.
4. On the Power Tools menu bar, select the Sigma option (Σ) and choose Function by Color from the options.
5. Select the SUM function from the Use function list.
6. Enter the range containing the colored cells you want to sum and select the color of the cells.
7. Click the Insert function button.

In just a few seconds, Google Sheets will sum the colored cells in your selected range and return the result. Trust me, it’s a fun way to get the job done. With a little practice, you’ll become a pro at summing colored cells.

How to Sum Colored Cells Using the App Script Function

Another method to sum colored cells in your dataset is by using the App Script function. Keep in mind that App Scripts require some basic knowledge of JavaScript, as the code is based on it. However, even if you’re not well-versed in JavaScript, you should be able to work with it just fine.

To access the Scripts Editor, go to the Tools menu and select the Script Editor option. If that doesn’t work, try going to Extensions > App Scripts. This will take you to the main page where you can add your code and save it easily.

Here’s the code you need to use:

/** 
 * This function will return the count of cells in the given range of cells based on a given color 
 * @param1 range - This is the range of cells to count. 
 * @param2 cell - This is a cell reference with the color to count. 
 * @returns the count of colored cells 
 * @customfunction 
 **/
function COUNTCOLOR(range, cell) {
  // Get the formula from the current cell
  var currRange = SpreadsheetApp.getActiveRange();
  var currSheet = currRange.getSheet();
  var currCellFormula = currRange.getFormula();

  // Parse the first parameter and convert to a range reference
  var para1 = currCellFormula.match(/((.*),/).pop();
  var countRange = currSheet.getRange(para1);
  var countRangeBG = countRange.getBackgrounds();

  // Parse the second parameter and convert to a range reference
  var para2 = currCellFormula.match(/,(.*))/).pop();
  var goalCell = currSheet.getRange(para2);
  var goalCellBG = goalCell.getBackground();

  var counter = 0;

  for (var i = 0; i < countRangeBG.length; i++) {
    for (var j = 0; j < countRangeBG[0].length; j++) {
      if (countRangeBG[i][j] === goalCellBG) {
        counter++;
      }
    }
  }

  return counter;
};

Simply copy the code into the App Scripts editor and save it. But before you do that, let me give you a little insight into how this code works.

The code is a custom function written in Google Apps Script. It takes two arguments: range and cell. range represents the range of cells on which the function should be applied, while cell contains the desired color. The function then parses these arguments and uses them to obtain the range and cell color.

Next, a variable named SUM is initialized and assigned a value of zero. The function loops through all the cells in the range to check if they match the background color of the cell argument. If there’s a match, the cell value is added to the SUM variable.

Once the range ends, the function returns the sum of the values that match the background color of the cell argument. As a result, we can obtain the sum of colored cells in our dataset using the SUMCOLOR function.

To use the SUMCOLOR function in Google Sheets, use the following syntax:

=SUMCOLOR(range, cell)

• range specifies the range on which the calculation should be performed.
• cell is the argument that specifies the color you want to sum cells by.

Enter the arguments for the function in the spreadsheet, like this:

=SUMCOLOR(D2:D11, A10)

The formula will return the sum of colored cells in your dataset. The first parameter is the range to be summed, and the second parameter is the cell reference that contains the background color you want to sum.

Isn’t this an interesting method? Give it a try!

Conclusion

Knowing how to sum colored cells in Google Sheets can be extremely helpful for quickly analyzing data. Highlighting cells not only makes your spreadsheet stand out, but also enhances data readability and engagement.

In this article, we explored three methods: using the SUBTOTAL function, add-ons, and the App Script function. These solutions are surprisingly easy to use. With a little practice, you’ll master these methods in no time.

So, what are you waiting for? Start using these methods now to sum colored cells in your dataset. Your future self will thank you.

Mr Reviews