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

  1. Go to Add-ons > Power Tools > Start in the Google Sheets menu:
    Run Power Tools.
  2. Click on the tool's icon and pick the Function by Color option:
    Find the add-on on the smart toolbar.

How to count and sum values by fill and font color

You will see a pop up window with a few settings to adjust:
Fine-tune the options to calculate exactly what you need.

  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:
    Pick the range to calculate within.

    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:
Refresh results if the changes took place in your spreadsheet.
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.

Reply

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.
Also, if it's possible, please share your spreadsheet with us (gapps.ablebits@gmail.com) with a description of your task. I kindly ask you to shorten the table to 10-20 rows.

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

Reply

it does not work for me

Reply

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.

Reply

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".

Reply

Igor,

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

Thanks

Reply

Hello, Alex,

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

Would it be possible for you to share your spreadsheet with us (gapps.ablebits@gmail.com) with a description of your task?
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.
Please compare:
Error: =SUM(valuesByColor("#ff00ff00"; "#000000"; '1'!A3:D10))
Correct: =SUM(valuesByColor("#00ff00"; "#000000"; '1'!A3:D10))
If you delete extra "f", everything work properly)

Reply

Thank you very much for your comments, Igor.

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.

Reply

Ask a question

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.