Count by Color add-in: COUNT, SUM, AVERAGE, MIN, MAX by cell background or 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

 

Add "Count by Color" to Excel 2016, 2013, 2010, 2007

Works in: Microsoft Excel 2016 – 2007, desktop Office 365 (both 32 bit and 64 bit).

Download XLTools Add-in

Count cells by color ribbon button

 

Learn your way around:

 

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. Choose to place the results on a new or on an existing worksheet.
  6. 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 colors
    • 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
  • SUM – sum 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? Please leave your comment below.

 

Any questions or suggestions? Please leave your comment below.

Leave a Reply

Your email address will not be published. Required fields are marked *