by Natalia Sharashova, updated on
If you color-code your data in Google Sheets, you've probably noticed the lack of standard formulas that summarize this kind of information. This is why we added a custom function that considers the font and background color of the cells when applying all basic operations.
You can find it among the smart features of our Power Tools add-on or as an individual tool: Function by Color. Both offer a 30-day trial period and two subscription plans: 12-month and lifetime.
Tip. Watch this short demo video or look through the detailed description with the screenshots below.
Let's take one of the most common tasks with colored cells in a spreadsheet: counting cells that have the same formatting. Say, here I have a list of grades and I want to see how many times each test was passed, i.e. count all green cells in a column:
If you work with similar ranges that comprise different types of records: numbers, text, or date values, embrace the COUNTA function with the Function by color tool.
For a start, open the tool to sum by color. In Power Tools, you will see it right on the smart toolbar:
Here are six simple settings you need to specify to get the results:
Once I click Insert function, the tool adds the formula under each column, so I will see the results for each test:
Let me break down its syntax and explain each part:
Say, I'm keeping track of classroom equipment orders. I denote the ordered things by yellow background color, items on the way by blue, and delivered equipment by green:
My task is to see how many desks, computers, and other supplies are on the way at the moment. Color is the only difference these numbers have in my table. So I open the tool to sum by color and use the following settings:
Click Insert function to get the formula after each line in your table.
The convenience of getting the formula is that you can modify any of its parts and paste it wherever you need in your Google spreadsheets.
Do you remember that Google Sheets don't have functions that work with color? This means they don't count changes to cell formatting as a reason to re-calculate your formula results. The good news is that both workarounds you can use are very simple.
Another way to change the result is to edit the entire formula. But if there's only a couple of settings you'd like to choose, you don't have to build the entire formula anew. It is enough to click a cell that already contains a formula and pick the option to edit it:
The add-on will open with all the setting that were used to create the selected formula. You can adjust some or all of them – ranges, colors, pattern cell, function – and insert everything back.
If you see an error in place of the formula, you may happen to be working with a file that has no locale. When this happens, our function doesn't know what delimiters it should use, so it ends up giving you an error. If you see this, please go to File > Spreadsheet settings... in Google Sheets and make sure you have the locale set.
Table of contents