Function by Color for Google Sheets
Sum and count cells by color: font, background, or both
This add-on will sum and count colored cells in Google Sheets if they share background or text hues. It will process your data in different ways: check cell color and count blanks; find the maximum, minimum, or average values, and more. The tool also recognizes existing formulas so you could quickly review and edit shades and even ranges used for calculations.
Try it for 30 days for free
With Function by Color you will
- Sum and count cells by one color Specify fill and/or font color to calculate only cells that look the same.
- Tally cells by both text and background colors Process only cells with the same fill and font hues.
- Set up pattern colors Pick a cell from the sheet to use its colors for calculations or choose the hues from special palettes.
- Fill results with your pattern colors Color cells with the results according to pattern hues used for calculation.
- Sum and count all coloured cells in Google Sheets Get the results for each fill or font color that exists in the range.
- Count cells either by font or fill color Check cells for text hues ignoring the background and vice versa.
- Calculate in each row, column, or your entire range Have one formula work out the entire range or create multiple formulas for each row or column at once.
- Calculate colored cells in 13 different ways Count, sum, find the average, min or max number, etc. – all based on cell colors.
How to count cells by color in Google Sheets
Calculate cells by their hues
in 3 different ways
Ablebits Docs
Have a look at online help pagesLicense benefits
Our customers get
30-day money-back guarantee
Frequently asked questions
What is Function by Color and what do I need it for?
If you color-code your data, this will be an indispensable tool in calculating cells that share hues. The add-on checks cell or text color in Google Sheets and does the math. It sums, multiplies, counts the number of cells, finds the average, maximum or minimum value, and more.
Function by Color also recognizes existing formulas letting you edit them quickly and put them back on the sheet with changes.
Isn't there a free way in Google Sheets to count colored cells?
As a matter of fact, there is not.
Though there are standard ways of sorting and filtering by color, Google Sheets doesn't know how to calculate numbers from cells with the same background or font color.
Luckily, our add-on is there to help you. :)
How to count colored cells in Google Sheets by a pattern cell?
Open Function by Color, go to the One color tab and adjust the following settings:
- Select the range with your color-coded cells.
- Set up the pattern cell or your own colors to check for.
- Pick the function for calculation.
- Decide where to place the results.
- Choose to calculate such cells in the entire selected range, in each column, or in each row.
- Click Insert function.
See a more detailed description here.
Does your add-on process multiple colors at a time?
Yes. The All colors tab is designed for the add-on to calculate all colored cells in your range. It returns the results for each color there is.
Do your formulas recalculate themselves if anything changes in my data?
Yes – if you change values. All formulas will recalculate themselves automatically.
No – if you change colors only. Google Sheets doesn't consider changes in color as a reason to update results in formulas. For this case, we introduced our own Refresh results option.
Can I count empty yet colored cells with your add-on?
Yes. There's a special function for that – COUNTBLANK.
How do I set a pattern cell to sum and count by its colors?
You have 3 options here:
- select a cell from your sheet using a color picker
- select hues of interest from the palettes that the add-on offers for both font and background
- enter the hex code of the color to a corresponding field
The pattern cell is available only for calculations from the One color tab.
What if I want to calculate data in each column? Should I copy your formula manually?
No. It's up to you whether to process the entire selected range, or each column or even row in it individually. In the latter case, the add-on creates all formulas with all the results under each column or next to each row automatically.
Is there a quick way to recognize colors standing behind each result?
Yes. Just make sure to use the very last option – Fill results with your pattern colors – and all cells with formulas will be highlighted the way their calculated cells are.
This option is also available only for calculations from the One color tab.
What are those extra built-in functions? Does the add-on create formulas with them?
The extension features two extra functions – CELLCOLOR and VALUESBYCOLORALL.
Though the add-on doesn't build formulas with them directly, you can use them in your own formulas to consider colored cells, e.g. COUNTIF, SUMIFS, etc.
You will learn more about them here. Formula examples are included.
System requirements
Operating Systems- Windows: Windows 10 and up
- Mac: macOS Catalina 10.15.7 and up
- Chrome OS™
Browsers- Chrome™
- Firefox
- Safari (Mac only)