Count and sum by color in Google Sheets

Function by Color allows you to calculate cells coloured in a certain way in any range in Google Sheets.

Video: How to sum and count colored cells in Google Sheets

Before you start

Note. Go to File > Settings in the Google Sheets menu and see if you have a locale selected there. If not, please set one and confirm your action.

To our regret, Google Sheets takes some time calculating custom formulas. 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 may also work a bit slow since it recalculates all custom formulas created by the tool in the current tab.

How to count and sum values by fill and font color

Start Function by Color

Go to Extensions > Function by Color > Start in the Google Sheets menu to access the utility: Start Function by Color.

Tip. You will also find the add-on in Power Tools — a collection of all Ablebits add-ons for spreadsheets:

  1. Open Extensions > Power Tools > Start in the Google Sheets menu: Run Power Tools.
  2. Then either click on the tool icon and pick the Function by color option: Find the add-on on the smart toolbar.

    Or access the add-on from the Process group: Function by Color in the Process group.

Count and sum values by one fill and font color

The first tab lets you calculate values based on one selected fill and/or font color: Calculate by one color.

  1. Define the range of cells where you want to calculate colored cells.
    Tip. Select a range of cells before running the tool and it will be picked up for processing. But if you run the add-on with a single cell being active in the sheet, that cell will be taken as a place for the result.

    To change the range, just enter it manually or click the Select range icon. The latter will open a special window allowing you to pick the necessary cells: Pick the range to calculate within.

    Tip. Use the Auto select button for the tool to identify your table (a used range till the first blank row and column) automatically.
  2. Decide on the background and font colors that you want to calculate:
    1. Click the Color picker button and you will get a pop-up window inviting you to select a pattern cell from your sheet: Select a pattern cell directly from the sheet.

      Just click the cell of interest in your table and then hit OK.

    2. Once the pattern cell is chosen, the Font color and Background color icons in the add-on will feature the corresponding hues.

      The checkboxes next to these icons let you decide if you want to calculate cells that share Font color only, or Background color only, or both hues.

      Tip. You can change these text and fill colors or set them from the beginning. Just click the corresponding icon and either pick the required shade from the palette or enter its hex code in a special field: Set a background hue using the palette.
  3. Single out the function you want to use for calculations from the Use function drop-down list. You can choose between the following functions: SUM, COUNT (to count numeric values only), COUNTA (to count both numeric and textual values), COUNTBLANK, 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. Select or enter the destination cell for the Function by Color formula in the Paste results to field. You can select the top-left cell for the results and the add-on will automatically adjust the range based on the next option.
  5. Use the Calculate in list to do one of the following:
    • insert one formula for the entire range: Calculate the entire range.
    • calculate each column individually: /Calculate in each column.
    • count colored cells in each row in the range respectively: Calculate in each row.
      Tip. This way, if you want to check how many green cells there are in each row, you won't have to copy the formula manually.
  6. Tick off the last checkbox to have the add-on fill results with your pattern colors (hues that are used for calculations).
  7. Here you can learn more about the additional custom functions that come along with the tool. Use them in your own formulas whenever the color of the cells matters. Jump to this section to get to know them.
  8. If you don't use the last two options often (Calculate in and Fill results with your pattern colors), click the icon with the upward pointing arrow to hide them. You can make them visible again at any time by clicking the arrow once more.

Hit Insert function to calculate all cells in the desired range based on the selected colors.

Note. To our regret, Google Sheets takes some time calculating custom formulas. 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.

Count and sum by all fill or font colors in the range

The second tab calculates cells of all colors that exist in your selected range: Sum and count values of all colors in the range.

  1. Identify the range where you want to process colored cells.
    Tip. If you select it in the table before running the tool, the add-on will pick it up automatically. If only one cell is selected upon running the extension, that cell will be taken as a place for the result.

    You can change the range by typing it manually directly into the field. Or click the Select range icon and pick the required cells from this special window: Another way to specify the range.

    Tip. Hit Auto select and the add-on will automatically recognize your used range: all data till the first empty row & column.
  2. Specify whether you want to calculate cells by their font or background colors.
  3. Pick one of 13 aggregate functions for calculations: SUM, COUNT (to count numeric values only), COUNTA (to count both numeric and textual values), COUNTBLANK, AVERAGE, AVERAGEA, MIN, MAX, PRODUCT, MODE, STDEV, VAR, MEDIAN.
  4. Choose cells where you want to get the result.
    Tip. Select the top-left cell for the results and the add-on will automatically adjust the range based on the next option.
  5. The Calculate in option lets you tweak where to sum and count colored cells exactly:
    • calculate the entire range with a single formula: Sum or count colored cells in the entire range.
    • process cells in each column separately: Count or sum colored cells in each column.
    • or handle colored cells in each individual row: Calculate colored cells in each row.

    This is very helpful since you won't have to copy the formula to sum or count colored cells in each column/row respectively.

  6. If you want to process colored cells in some other formulas, e.g. IF(S), SUMIF(S), etc. make use of these extra custom functions, they are built into the tool. Learn more about them in this section.
  7. If the last option doesn't really matter, you can hide it along with the info about those extra functions using the upward-pointing arrow icon. No worries, you can make them visible again at any time by clicking the arrow once more.

Hit Insert function to calculate cells of all colors in the selected range.

Note. Google Sheets takes some time to calculate custom formulas. If you have a large table, please wait a bit till the result appears.

How to update the results

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

If there are any changes only to the formatting in the range, there are 3 ways to update calculations:

  • If you use standalone Function by Color, go to Extensions > Function by Color > Refresh results: Refresh results from the single add-on menu.
  • In Power Tools the Refresh results option resides in two places:
    • In the Smart Toolbar drop-down list: Refresh results if the changes took place in your spreadsheet.
    • And in the Google Sheets menu — Extensions > Power Tools > Refresh results for Function by color: Refresh results from Power Tools menu.
    Note. The Refresh results option may work a bit slow since it recalculates all custom formulas created by the tool in the current tab.
  • One more way to make the formulas recalculate themselves is to change one of the values in the calculated range, e.g. add or remove a character.

How to edit existing formulas

The add-on offers an opportunity to quickly edit existing formulas: change colors and a way to calculate, pick another function or even source range.

Just select a cell with a formula you want to adjust and click the corresponding option from the Function by Color menu: Extensions > Function by Color > Edit selected formula. Edit selected formula.

Tip. If you use Power Tools, you will find the option in the drop-down list on the Smart Toolbar: Edit formula from Power Tools.

The add-on pane will open, featuring all settings that were used to build this formula. Tweak whatever seems necessary and hit Insert function to get the updated result in the same cell.

Additional custom functions embedded into Function by Color

Besides the formula that our Function by Color creates for you, there are 2 extra functions available:

Not only do they work with colors, but also are useful for SUMIF(S) and COUNTIF(S).

Note. For custom functions to work, you need to have Function by Color or Power Tools installed. The same goes for other users you share your spreadsheets with: for them to see the results, they need to have Function by Color or Power Tools installed as well.
Tip. Click here to make your copy of the file from this video and follow the tutorial along.

CELLCOLOR

Returns RGB codes or the names of colors (per Google Sheets color palette) used in each cell of the range.

Syntax

CELLCOLOR(range, [color_source], [color_name])

  • range — The range to check for colors.
  • color_source — [optional — "font" by default] — The source of the colors to check:

    • "fill" gets the background color of each cell in the range.
    • "font" (default) gets the text color of each cell.
    • "both" returns font and fill colors for each cell, for example, '#ffffff, #f4cccc' or 'white, light red 3'.
  • color_name — [optional — TRUE by default] — TRUE or FALSE indicating whether to return color names (per Google Sheets color palette) or their RGB codes.
    • TRUE returns color names, for example, 'light red 3'.
    • FALSE returns RGB codes, for example, '#f4cccc'.

Sample Usage

  1. Return fill colors for column Profit:

    =CELLCOLOR(C2:C10,"fill",TRUE) CELLCOLOR function returns the name of the fill color for each cell in the range.

  2. Pay a 10% bonus for all deals colored green:

    =IF(CELLCOLOR(C3,"fill",TRUE)="light green 3",0.1,"") Use CELLCOLOR in tandem with the IF function.

  3. Count green cells from column C belonging to Leela in column A:

    =COUNTIFS(A2:A10,"Leela",CELLCOLOR(C2:C10,"fill",TRUE),"light green 3") COUNTIFS uses VALUESBYCOLORALL to count all green cells from column C belonging to 'Leela' in column A.

Notes

  • When embedding CELLCOLOR in SUMIF(S), make sure to use it only as a criteria_range. Due to the SUMIF(S) requirements, it will take no custom function as a sum_range and such formulas will return errors.
  • For the function to work, you need to have Function by Color or Power Tools installed.

VALUESBYCOLORALL

Returns the entire range where only cells with the same fill and font colors contain values, while other cells remain empty. Can be used as a criteria_range in SUMIF(S), range in COUNTIF(S).

Syntax

VALUESBYCOLORALL(fill_сolor, font_сolor, range)

  • fill_color — RGB code or color name (per Google Sheets color palette) for a background color.
    Tip. Use a pair of double quotes "" to ignore fill color:
    =VALUESBYCOLORALL("light green 3","",B2:B10)
  • font_color — RGB code or color name (per Google Sheets color palette) for a font color.
    Tip. Use a pair of double quotes "" to ignore font color:
    =VALUESBYCOLORALL("","black",B2:B10)
  • range — The range to check for cells within.

Sample Usage

  1. =VALUESBYCOLORALL("#b7e1cd", "#000000", B2:B20)
    or
    =VALUESBYCOLORALL("light green 3","black",B2:B10) How VALUESBYCOLORALL works.
    Tip. When either font or fill color is not important, enter double quotes instead to ignore them:
    =VALUESBYCOLORALL("light green 3","",C2:C10) Ignore font color and return values from cells colored green.
  2. Sum numbers from green cells belonging to Leela from column A:
    =SUMIFS(C2:C10,A2:A10,"Leela",VALUESBYCOLORALL("light green 3","black",C2:C10),"<>") SUMIFS uses VALUESBYCOLORALL to sum numbers from green cells belonging to 'Leela' in column A.

Notes

  • When embedding VALUESBYCOLORALL in SUMIF(S), make sure to use it only as a criteria_range. Due to the SUMIF(S) requirements, it will take no custom function as a sum_range and such formulas will return errors.
  • For the function to work, you need to have Function by Color or Power Tools installed.