How to count by color and sum cells colored using conditional formatting

If you have applied conditional formatting to color cells based on their values and now you want to count cells by color or sum the values in colored cells, I have bad news - there is no universal user-defined function that would sum by color or count color cells and output the resulting numbers directly in the specified cells. At least, I am not aware of any such function, alas : (

Of course, you can find tons of VBA code on the Internet that attempts to do this, but all those codes (at least the examples I've come across, do not process conditional formatting such as "Format all cells based on their values", "Format only top or bottom ranked values", "Format only values that are above or below average", "Format only unique or duplicate values". Besides that nearly all those VBA codes have a number of specificities and limitations because of which they may not work correctly with certain workbooks or data types. All in all, you can try your luck and google for an ideal solution and if you happen to find one, please do come back and post your finding here!

The VBA code below overcomes the above mentioned limitations and works in Microsoft Excel 2010, Excel 2013 and Excel 2016 spreadsheets with all types of condition formatting . As a result, it displays the number of colored cells and the sum of values in those cells, no matter which type of conditional formats are used in a sheet.

How to use the code to count colored cells and sum their values

1. Add the above code to your worksheet as explained in the first example.
2. Select a range or ranges where you want to count colored cells or/and sum by color if you have numerical data.
3. Press and hold Ctrl, select one cell with the needed color, and then release the Ctrl key.
4. Press Alt+F8 to open the list of macros in your workbook.
5. Select the SumCountByConditionalFormat macro and click Run.

As a result, you will see the following message:

For this example, we selected the Qty. column and got the following numbers:

• Count is the number of the cells with a particular color, a reddish color in our case that marks "Past Due" cells.
• Sum is the sum of values of all red cells in the Qty. column, i.e. the total number of "Past Due" items.
• Color is the Hexadecimal color code of a selected cell, D2 in our case.