Video: How to count colored cells in Google Sheets

If you haven't tried our Function by Color add-on for Google Sheets yet, you will be pleasantly surprised. It calculates values based on their font, fill, or both colors using 13 aggregation functions. Today's video shows you how it works.

Video transcript: Function by Color add-on for Google Sheets

When we look at our spreadsheets, we often use colors for additional context.

Function by Color is a special tool that will help you digest this information and perform color based calculations:
Process cells by common color.
It comes as a part of Power Tools as well as a standalone add-on.

How to count values by their cell colors in Google Sheets

Let's look at a quick example.

Here I'd like to sum up the test results based on color.

Run the add-on

I'll go to Add-ons and start Function by Color or Power Tools. And then I'll use a shortcut here at the top to open Function by Color:
Run Function by Color from Power Tools.

Select the range to process

First, I select the range with colored cells I wish to summarize.

Pick the colors you want to count

Then I pick the first color I want to count. I care only about the fill color here but you can consider font color as well.

There are all possible ways to define it:

  • choose it from the drop-down list
  • enter its code
  • but perhaps the easiest one is just pointing to a cell

Select fill color from the color palette.

Choose the function

Next, I choose the function to apply. One thing to keep in mind though is if you want to count cells with text – go for the COUNTA function.

Decide where to place the result

Now I'll pick the first cell to paste the result.

Tweak additional options

The default way to count is the entire range of course but what I need now is check the results in each section of the test.

Look at this arrow below the field. It will open up a couple of additional options.

One will make it easier if you perform the same calculations in every column or in every row. So I will choose each column and thus pick the row for the results.
Calculate values in rows, columns, or the entire range.
When you work with multiple colors, use the last box to apply the pattern cell color to the results so you can see what it actually counts right away.

See the result

So all I have left to do is click Insert function and get the count.

To do the same for all red cells, I simply pick a new color, and the destination, and click the button.

How to update the results returned by Function by Color

There's one thing to keep in mind though. When you come back to your spreadsheets and you update only colors in your range, the calculation results are not updated. They can only change with values.

That's said, it can be easily resolved. You can simply update a value or use this Refresh results option in the quick access bar:
Refresh results in Function by Color.

If you'd like to use color in your calculations, go to Add-ons > Get add-ons to open Google Workplace Marketplace and install Function by Color or Power Tools add-on.

And feel free to contact us with any questions. We're happy to help!

You may also be interested in