askvity

How to Sum Colored Cells in Google Sheets?

Published in Google Sheets Formulas 3 mins read

Summing cells based on their color in Google Sheets can be achieved using a combination of filtering and the SUBTOTAL function. This approach allows you to dynamically sum cells of a specific color without relying on custom scripts.

Here’s how you can do it:

  1. Set up your data: Ensure your sheet has the data you want to sum, with cells colored as desired.
  2. Create a Filter:
    • Select the data range (avoid selecting the total row, as stated in the reference).
    • Right-click on the selected range.
    • Choose "Create a filter". A filter icon should appear in the header row.
  3. Filter by Color:
    • Click the filter icon in the column that contains the colored cells.
    • Navigate to "Filter by color" and select the fill color you want to sum.
    • Now, only the cells with that specific color will be visible.
  4. Use the SUBTOTAL function:
    • In an empty cell, enter the formula: =SUBTOTAL(9, range).
    • Replace range with the column range of the cells you are filtering. For example, if you are summing data in column B, and the column starts on row 2, the range would be something like B2:B.
    • SUBTOTAL with the number 9 refers to the SUM operation. Unlike the SUM function, SUBTOTAL will only add visible cells after filtering, thus summing the colored cells.

Example:

Let's say you have a column with some numbers, and certain cells are highlighted in yellow.

  • You filter your column to show only yellow cells.
  • You enter =SUBTOTAL(9, B2:B) in another cell.
  • The cell containing this formula will display the sum of all yellow cells. If you change the filter to another color, the subtotal will automatically recalculate for that color.

Key Takeaways:

  • This method does not require any coding or scripts.
  • The SUBTOTAL function is essential as it only sums visible cells. The SUM function will still add all cells in the range, whether filtered or not.
  • As shown in the reference video, you can select your data (without the total row), create a filter, and then use the SUBTOTAL function to sum based on your color filter.

This is a straightforward approach to summing colored cells in Google Sheets that's practical and effective.

Related Articles