Calculate COUNT, SUM, AVERAGE, MIN, MAX by cell background color or by font color

When working with Excel tables, it is common to use different cell shading or font colors. Highlighting cells brings attention and serves as a color code. E.g. you may choose to apply green background to showcase successful metrics, or assign red font to figures to signal a warning. However, an obvious task to count and sum up values with similar colors may turn to hours of programming macros or formulas.

The Count by Color add-in instantly and without VBA aggregates values by any type of color code:

  • Calculate COUNT, SUM, AVERAGE, MIN, MAX for each color
  • Aggregate by fill color and/or by conditional format color
  • Calculate by cell background color or by font color
  • Preview and paste the resulting summary table into a worksheet

How to calculate cell values based on their background fill color

Excel treats differently two types of cell background coloring: custom solid fill color (when you assign the color) and conditional color (when conditional formatting is applied). With the add-in you can aggregate values based on any type of background color:

  1. Click theCount by Colorbutton on XLTools ribbon Select the range.
    Tip:click any cell in a table and it will be automatically selected.
  2. From the "Acknowledge only" drop-down list, choose which type of coloring to take into account in further calculations:

    • Custom colors– to acknowledge cells only with solid fill colors
    • All colors– to acknowledge all cells, both with custom and conditional coloring
  3. From the "Count by color of" drop-down list, selectBackground.
  4. Preview results in a table of aggregate calculations: Count, Sum, Average, Min, Max – with breakdown by color.
  5. Click OK Done.
Count values by cell background color

How to calculate cell values based on their font color

Excel treats differently two types of font coloring: custom font color (when you assign the color) and conditional font color (when conditional formatting is applied). With the add-in, you can aggregate values based on any type of font color.

  1. Click theCount by Colorbutton on XLTools ribbon Select the range.
    Tip:click any cell in a table and it will be automatically selected.
  2. From the "Acknowledge only" drop-down list, choose which type of coloring to take into account:

    • Custom colors– to acknowledge cells only with custom color
    • All colors– to acknowledge all cells, both with custom and conditional coloring
  3. From the "Count by color of" drop-down list, selectFont Color.
  4. Preview results in a table of aggregate calculations for each color: Count, Sum, Average, Min, Max – with breakdown by color.
  5. Choose to place the results on a new or an existing worksheet.
  6. Click OK Done.
Count values by font color in Excel

How to calculate cell values based on their conditional colors

Excel treats differently two types of cell background coloring: custom solid fill color (when you assign the color) and conditional color (when conditional formatting is applied). Conditional formatting may be applied both to font and cell background. With the add-in you can aggregate values based on any type of conditional color:

  1. Click theCount by Colorbutton on XLTools ribbon Select the range.
    Tip:click any cell in a table and it will be automatically selected.
  2. From the "Acknowledge only" drop-down list, choose which type of coloring to take into account in further calculations:

    • Conditional colors– to acknowledge cells only with conditional formatting colors
    • All colors– to acknowledge all cells, both with custom and conditional coloring
  3. From the "Count by color of" drop-down list, choose eitherBackgroundorFontdepending on type of conditional formatting.
  4. Preview results in a table of aggregate calculations: Count, Sum, Average, Min, Max – with breakdown by color.
  5. Choose to place the results on a new or on an existing worksheet.
  6. Click OK Done.
Count values by conditional color in Excel

Aggregate calculations available (Count, Sum, Average, Minimum, Maximum)

The add-in applies the most frequent aggregate functions, based on cell background or font color:

  • COUNT – count of all values in the range by color
  • AVERAGE – average (arithmetic mean) of all values in the range by color
  • MIN – the smallest value in the range by color
  • MAX – the largest value in the range by color

How cells and values are processed in calculations

The Count by Color add-in automatically detects and lines up all colors in the range. This includes default black color – this way, you can compare results for colored and black font values.

  • Taken into account are: numeric values, as well as formulas, functions and cell references that return a numeric value. Blank cells or any cells that contain text, dates, or errors will be ignored.
  • The add-in ignores hidden rows or columns, i.e. calculations are based only on visible cells. If you need to calculate an entire range, please unhide rows/columns and clear filters.
  • Pasted into a worksheet, the summary table with aggregate results contains calculated values (not links or formulas).
  • Merged cells are treated as a single cell.
Any questions or suggestions?
close window
How can we help?