When you use different colors to organize your data in Google Sheets, you might find there are no built-in formulas to easily summarize data based on color. To address this, we introduced several custom functions that take into account both the font and background color of cells for basic operations, making it possible for your Google Sheets to sum by color or count cells by color.
You will find these functions among the smart 40+ features of our Power Tools collection or as an individual tool: Function by Color. Both offer a 30-day trial period and two subscription plans: 12-month and lifetime.
How to count colored cells in Google Sheets
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're dealing with ranges that include various data types like numbers, text, or dates, consider using the COUNTA function from 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:
- The range with the color-coded data that you want to check: I pick the columns with the test results, B2:D18.
- The pattern cell. The function will take its font and/or background color and look for the same in other cells. I pick the fill color of B2 as an example.
- The function. To count cells with a particular number format, it's best to choose the COUNTA function because it is the only one that works with non-numeric values. This way you can be sure that the data format in the cells will not impede the results.
- The place for results. I want my Google Sheets count cells by color in each column so it is three cells for me, B19:D19.
- How to apply it. You can choose to count cells in each row, column, or in all selected cells. As I want to count green cells for each test, I choose to Calculate in each column. If I wanted a general result for all three tests, I'd choose the entire range.
- The formatting of cells with the result. You can have them filled with the same colors as you count by selecting just one checkbox.
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:
- =LAMBDA(vbc, IF(ISERROR(vbc), vbc is the service part that enables the formula to return exactly what Google Sheets replies with, whether it's "loading", some error, or the calculation result.
Tip. You'll see this part only for COUNT, COUNTA, and COUNTBLANKS functions because of their peculiarities.
- COUNTA(vbc) is one of standard Google Sheets functions you pick to use with the colored cells. vbc stands for valuesByColor.
- valuesByColor is our custom function that handles colors.
- light green 3 is the cell color considered for the calculations, i.e. the background color of the pattern cell.
- "" is empty since I don't consider the font color of the pattern cell.
- D2:D18 is the range to check for colored cells. In my example, it looks at cells in column B, C & D respectively.
How to sum colored cells in Google Sheets
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:
- I select the entire range with my data to check it, B2:F8
- I pick C2 as a pattern cell to specify the format of items I want to calculate.
- Select the SUM function to tally numbers from blue cells.
- Pick cells to place the resulting calculations for each item, G2:G8
- To see the number of shipped items for every product, apply the function to each row.
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.
Google Sheets: sum by all colors in the table
The updated Function by Color lets you calculate not just one color per formula but all colors simultaneously.
Using the same data as above, I can find the total of all delivered supplies (green), tally those on their way (blue) or those ready for ordering (yellow).
I just need to switch to the All colors tab in the add-on and adjust the same settings:
- The range is the same table, B2:F8.
- I will sum data by all fill colors.
- I also choose to calculate the entire range and have 1 formula inserted in A10.
Note. The formula returns the result in 2 columns: color & the total. So make sure there's enough space to insert the function. The result will show once there's room for it, but until then you may see the #REF! error if other data is on the way.
Update the results
So, Google Sheets doesn't have native functions that work with color. This means your spreadsheet doesn't treat changes to cell formatting as a reason to re-calculate your formula results. The good news is that both workarounds in-store are elementary :)
- You can change any value within the calculated range. Say, you are counting green cells in A1:C254. You can simply add a character to any of the cells in this range, and then remove it to get the updated results.
- If you have a lot of valuesByColor formulas in your sheet, click on the Refresh results option (right under the Function by color in Power Tools or under the Extensions menu for the single add-on) to update all formulas in one go.
Another way to change the result is to edit the entire formula. But if there are 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 settings necessary to create this selected custom 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 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 > Settings in Google Sheets and make sure you have the locale set.
Video: How to count colored cells in Google Sheets