Count and sum by color in Google Sheets

Function by Color is part of Power Tools' smart toolbar. The add-on allows you to calculate numbers formatted in a certain way in any range.

Before you start

To our regret, custom formulas calculations in Google Sheets take some time. If you have a large table, please wait a few seconds before the value of the built formula changes from "0" to a correct one.

The Refresh results option works in the same way - a bit slow, since it recalculates the add-on's custom formulas.

How to count and sum values by fill and font color

Start Function by Color

2. Click on the tool's icon and pick the Function by Color option:

How to count and sum values by fill and font color

You will see a pop up window with a few settings to adjust:

1. Click on the Select range icon within the Source range field to define the range of cells that you want to consider for calculation:

Tip. Use the Auto select button to identify the entire used range automatically.
2. Enter or select your Pattern cell - a cell with the background and font color you want to use for calculations.
Tip. Click on the eyedropper icon within the field to select the cell in your spreadsheet.
3. Pick the function you want to use in the formula from the Use function drop-down list. You can choose between the following functions: Sum, Count, Counta, Average, Averagea, Min, Max, Product, Mode, Stdev, Var, Median.
Tip. If your task is to count cells with a particular color, it is best to choose COUNTA as it is the only function that works with text values.
4. Use the Calculate in list to insert one formula for the entire range, add it for each column, or count colored cells in each row in the range respectively. So, if you want to check how many green cells there are in each row, you won't have to copy the formula manually.
5. Select or enter the destination cell for the Function by Color formula in the Paste results to field. E.g., if you sum colored values in each row or column, you can select the top and leftmost cell for the results.

Click Insert function to calculate the cells based on the selected color.

Note. To our regret, custom formulas calculations take some time. If you have a large table, please wait a few seconds before the value of the built formula changes from "0" to a correct one.

The Refresh results option works in the same way - a bit slow, since it recalculates the add-on's custom formulas.

How to update the results

Note. By default, changes of the background color are not considered as a reason to update formula results in Google Sheets. Unfortunately, we can't control or bypass that.

If there are any changes to the formatting in the range, you need to manually pick the Refresh results option in the Function by Color drop-down list to update calculations:

As an alternative, you can change one of the values in the calculated range, e.g. add or remove a character.

Responses

I am not able to do . I have 500 cells and I like to count 3 colors used . these are in 7 heads.

Hello, Hem,

Thank you for reporting a problem.

For us to be able to assist you better, please send us the screenshot of how your data is stored and of the result you're getting with our add-on.

We'll look into the problem and do our best to assist you.

it does not work for me

Hello, Dan,

I'm sorry to hear you've encountered difficulties with our tool.

Please email us to support@ablebits.com with the screenshots of your source data and the result you're getting after running the add-on.
We'll investigate the problem.

Thank you.

Hi,

I have found solution)

By some reasons, when you use POWER TOOLS in already formed table, SUM by color function doesn't "catch" cells color in "Source range" (even if cells have the same color and code e.g. #00ff00). After applying the function it give you "0".
You have two options:
1. Apply Google Sheet function "Paint format" from your "Pattern cell" to the cells in your "Source range".
2. Simulteniously select "Pattern cell" and correspondent cells in your "Source range" and apply to them the same color.
Don't forget "Refresh results" in POWER TOOLS after each actions!
After applying POWER TOOLS, all newcreated cells with correspondent color will be "catched".

Igor,

I think this is the issue I'm having in my google sheet. Where/how do I find "paint format" exactly?

Thanks

Hello, Alex,

I'm sorry you're having difficulties with the tool.

We'd look into the issue and do our best to make our add-on work for you.
Thank you.

I haven't found the reason, but in some cases Sum function give an error (#ERROR!).
The reason of the error is two extra "f" in front of first color code.
Error: =SUM(valuesByColor("#ff00ff00"; "#000000"; '1'!A3:D10))
Correct: =SUM(valuesByColor("#00ff00"; "#000000"; '1'!A3:D10))
If you delete extra "f", everything work properly)

I'm afraid, there's currently an issue that has appeared recently due to some problems on the Google side. For some reason, Google Sheets occasionally stops reading custom formulas made by other add-ons. To our regret, our Sum by Color was also affected, but flipping the ranges for calculation one way and back with our Flip tool usually helps.

However, "ff00ff00" is an incorrect color code. If this code was returned to you by our add-on, please share an example spreadsheet with us (gapps.ablebits@gmail.com) with the colors you were trying to count.

We'll check what may be causing the problem.
Thank you.