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:

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.

Download XLTools for Excel

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.

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.

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.

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

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.