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

Before you begin, add Count by Color to Excel

Count by Color is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Get started with XLTools ribbon
– free trial gives you 14 days of full access to all features.

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 the Count by Color button 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, select Background.
  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 the Count by Color button 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, select Font 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 the Count by Color button 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 either Background or Font depending 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?
Rajesh Menon Hi, please let me know if I can generate only the count of a specified (only one colour) colour in a specified cell. This is what I am looking for now for a report where I have to use 120 columns and need to mention the count of a particular colour on top of each data in the column. I will be grateful if you can email me the answer. Best regards, Rajesh Thanking you
July 15, 2019 at 11:03
Maria BalobanovaHi Rajesh. Sorry, at the moment the feature does not support the exact functionality you are looking for. You can create the report for all colors, and then insert the Count number for the specific color on top of your columns. I realize, this will requires some manual work. But thank you for the idea! We’ll consider this additional functionality for the next releases.
July 16, 2019 at 08:41
How can we help?