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
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.
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. :)
Open Function by Color, go to the One color tab and adjust the following settings:
See a more detailed description here.
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.
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.
Yes. There's a special function for that – COUNTBLANK.
You have 3 options here:
The pattern cell is available only for calculations from the One color tab.
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.
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.
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.