Count and sum by color in Google Sheets

Start Function by Color

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

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:

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:
   

   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:
      

      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:
      
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 each column individually:
     
   • count colored cells in each row in the range respectively:
     
     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.

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:

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:
   

   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:
     

   • process cells in each column separately:
     

   • or handle colored cells in each individual 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.

How to update the results

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

• Right from the add-on, using the Refresh results button at the bottom:
  
• From the Google Sheets menu:
  • For the standalone Function by Color, it's Extensions > Function by Color > Refresh results:
    
  • For Power Tools, it's Extensions > Power Tools > Refresh results for Function by color:
    
• In Power Tools, also in the Smart Toolbar drop-down list:
  
  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.

CELLCOLOR

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

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