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 the average values, and more. In addition, the tool will recognize existing formulas so you could quickly review and edit shades and even ranges used for calculations.

Try it for 30 days for free
Learn more
This tool is part of
Power Tools
This add-on is part of Power Tools: 30+ tools to boost your Google Sheets
Function by Color for Google Sheets

With Function by Color you will

  • Sum and count colored cells in Google Sheets Sum numbers & count empty or non-blank cells that share text or/and fill shade.
  • Tally cells by both text and background colors Process only cells with the same cell 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.
  • Decide where to place resulting formulas Choose any cell in your spreadsheet to have the result inserted there.
  • Calculate colored cells in 13 different ways Find the average, min or max number – all based on cell color.
  • 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.
  • Fill results with your pattern colors Color cells with the results according to pattern hues used for calculation.

How to count cells by color in Google Sheets

Check text color to sum similar cells
Check text color to sum similar cells
Count cells in Google Sheets by background color
Count cells in Google Sheets by background color
Check cell colors and find the minimum value by both font and fill hues
Check cell colors and find the minimum value by both font and fill hues
Calculate color-coded cells in each row
Calculate color-coded cells in each row
Calculate cells sharing the same colors in each column
Calculate cells sharing the same colors in each column
Process data with 13 different functions
Process data with 13 different functions
Use palettes or color-picker to set up pattern shades
Use palettes or color-picker to set up pattern shades

Calculate cells by their hues

in 3 different ways

Count cells in the entire range.
Calculate records in the entire range.
Calculate values in each row.
Calculate cells in each row.
Calculate cells in each column.
Sum cells in each column.

License benefits

Our customers get

Unconditional
30-day money-back guarantee
Free support
Volume discounts: the more you buy, the less you pay

These companies choose Ablebits products

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 back to 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?

Find Function by Color in Power Tools: either in the Smart Toolbar or the Process group, and adjust the following settings:

  1. Select the range with your color-coded cells.
  2. Set up the pattern cell or your own colors to check for.
  3. Pick the function for calculation.
  4. Decide where to place the results.
  5. Choose to calculate all cells in the entire selected range, in each column, or in each row.
  6. Click Insert function.

See a more detailed description here.

Does your add-on process multiple colors at a time?

In a way, yes. You will still need to create separate formulas for each set of text+fill color, but afterwards, you can combine them into a bigger SUM, COUNT, AVERAGE, etc. formula manually.

Do your formulas recalculate themselves if anything changes in my data?

If you change any value in a cell, all formulas will recalculate themselves automatically.

As for changes in color, by default, they are not considered as a reason to update results in Google Sheets. 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 colors for calculation?

You have two 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

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.

System requirements

Operating Systems

  • Microsoft: Windows 7 and up
  • Mac: Yosemite 10.10 and up
  • Chrome OS™

Browsers

  • Chrome™
  • Firefox
  • Safari (Mac only)
Try it for free
This add-on is part of Power Tools: 30+ tools to boost your Google Sheets

30-day fully functional version

Any questions or comments?

Please contact us here

Our working hours:
12:00 am - 10:00 am PST; 3:00 am - 1:00 pm EST; 8:00 am - 6:00 pm CET.

Your message was successfully sent.

Thank you for contacting us!
We will get back to you shortly.

You will receive a delivery confirmation for your message within a few minutes. If you don't get it, please re-send your message and make sure you entered a valid e-mail address.

Close