How to count by color and sum by color in an Excel worksheet

Suppose you have a table listing your company's orders where the cells in the Delivery column are colored based on their value - "Due in X Days" cells are orange, "Delivered" items are green and "Past Due" orders are red.

What we want now is automatically count cells by color, i.e. calculate the number of red, green and orange cells in the worksheet. As I explained above, there is no straightforward solution to this task. So, move on with the 5 quick steps below and you will know the number and sum of your color cells in a few minutes.

1. Open your Excel workbook and press Alt+F11 to open Visual Basic Editor (VBE).
2. Right-click on your workbook name under "Project-VBAProject" in the right hand part of the screen, and then choose Insert > Module from the context menu.

3. Add the following code to your worksheet: Code

4. Save your workbook as "Excel Macro-Enabled Workbook (.xlsm)". If you are not very comfortable with VBA, you can find the detailed step-by-step instructions and a handful of useful tips in this tutorial: How to insert and run VBA code in Excel.

5. Now that all "behind the scenes" work is done for you by the just added user-defined function, choose the cell where you want to output the results and enter the CountCellsByColor function into it:

CountCellsByColor(range, color code)

In this example, we use the formula =CountCellsByColor(F2:F14,A17) where F2:F14 is the range containing color-coded cells you want to count and A17 is the cell with a certain background color, a red one in our case.

In a similar way, you write the formula for the other colors you want to count, yellow and green in our table.

If you have numerical data in colored cells (e.g. the Qty. column in our table), you can add up the values based on a certain color by using an analogous SumCellsByColor function:

SumCellsByColor(range, color code)

As demonstrated in the screenshot above, we used the formula =SumCellsByColor(D2:D14,A17) where D2:D14 is the range and A17 is the cell with a color pattern.

In a similar way you can count cells and sum cells' values by font color using the CountCellsByFontColor and SumCellsByFontColor functions, respectively.

Note: If after applying the above mentioned VBA code you would need to color a few more cells manually, the sum and count of the colored cells won't get recalculated automatically to reflect the changes. Please don't be angry with us, this is not a bug of the code : ) In fact, it is the normal behavior of all Excel macros, VBA scripts and User-Defined Functions. The point is that all such functions are called with a change of a worksheet's data only and Excel does not perceive changing the font color or cell color as a data change. So, after coloring cells manually, simply place the cursor to any cell and press F2 and Enter, the sum and count will get updated. The same applies to the other macros you will find further in this article.