Sum and count by color in Excel

Start Sum by Color

1. Open the Excel worksheet where you need to count cells by color.
2. On the Ablebits Tool tab, in the Calculate group, click Sum by Color and select one of the two tools:
   • Sum & Count by One Color
   • Sum & Count by All Colors

   

Sum and count by one color

Use Sum & Count by One Color to count cells or calculate values based on the selected color.

Select the options on the Count and Sum by Color pane:

1. By default, the add-in picks the entire data range in your Excel worksheet. You can change it by selecting the needed table or entering the range address manually.
2. Also, you can get the entire table selected automatically. For this, pick any cell in the table and click the Expand selection icon.
3. Click the Color Picker icon and select a cell that represents the background and/or font color you want to sum and count by.
4. Click the Calculate button and get the result on the add-in pane.
   Note. If there are empty cells in your range, the tool will identify the font and fill colors that are set in the Format Cells option.
5. To see calculations by background color, go to the Background tab.
6. If you want to sum and count by font color, open the Font tab.
7. If needed, you can paste a single calculation result into your table: just hover over the needed function (SUM, COUNT, MAX, etc.) and click the Paste button. The add-in will ask you to pick a cell for the result:

   

   Note. If you use this option, the numerical result will be pasted into your table without any text labels. For your convenience, the resulting cell will have the same font/background color as the original cells you calculated.
8. Select Insert result as formula and formulas will be created with custom Ablebits functions. You can learn more about them on the Ablebits functions page. The functions don't work for conditionally formatted cells and for ranges formatted as tables with colored alternate rows or columns.
   Note. Formulas which are created with custom Ablebits functions recalculate only values and do not respond to color changes in cells.
9. To paste all results into your worksheet, click the Paste All... button and specify the top-left cell for the results.

For your convenience the results will have labels and the same font or background color as the cells you selected.

Sum and count by all colors

This tool will give you a quick overview of color-coded data in your table. For example, if you use different hues to highlight prices from vendors, the utility will sum cells by color and show you the total for any group of numbers.

To count colored cells in Excel, open the worksheet with your data, run Sum and Count by All Colors, and select the options on the tool pane:

1. The add-in selects the entire data range in your worksheet automatically. You can change the range by entering the new address manually or selecting it in you sheet.
2. Also, you can get the entire table selected automatically. For this, pick any cell in the table and click the Expand selection icon.
3. When the range is chosen, click Calculate and see the results straight on the pane.
4. Select Background to see the result calculated by the cell's fill color.
5. Choose Font to total the selected values by their font color.
6. To calculate, pick from the functions in the dropdown list: Sum, Count, Counta, Countcolor, Average, Averagea, Averagecolor, Max, or Min.

   Use the COUNTCOLOR function to get the total number of cells that have the same background or font color. Be aware that COUNT only counts cells containing numbers, COUNTA counts all cells that aren't empty. COUNTCOLOR counts all cells that have the appropriate font or background color regardless of the type of data the cells contain: text, numbers, or nothing:
   

   Use the AVERAGECOLOR function to get the average of all cells that have the chosen background or font color, empty as well. While AVERAGE returns the average of cells with numeric values only, AVERAGEA finds the arithmetic mean of all cells that are not empty, including logical values and text representations of numbers. AVERAGECOLOR returns the average of all cells that have the chosen background/font color, whether they contain values or are empty:
   

7. Select Insert result as formula and formulas will be created with custom Ablebits functions. You can learn more about them on the Ablebits functions page. The functions don't work for conditionally formatted cells and for ranges formatted as tables with banded rows or columns.
   Note. Formulas which are created with custom Ablebits functions recalculate only values and do not respond to color changes in cells.
8. To have the results pasted into your worksheet, click the Paste All... button and specify the top-left cell for the results. Once the location is chosen, click OK.

The results will have no text labels, but for your convenience they will have the same font or background color as the cells that were processed.