Let’s say you have many cells with different colors like red, green, blue, and so on, but now have to count how many cells in a range have a specific background color and add the cells of each similar color.

There is no direct formula in Excel that specifies the sum or number of colored cells. Here I will show you a way to solve this problem.

Count And Add Colored Cells in Excel

The following code can help you calculate and add cells with a specific background color:

1 . Press ALT + F11 to open the Microsoft Visual Basic for Applications window.

2 . Click Insert > Modules and copy VBA to the module.

VBA: count and add cells by the background color.

 Function Color Function (rcolor As Range, rrange As Range, Optional SUM As Boolean) 
Dim rCell As Range 
Dim LCol As Long 
Dim vResult 
LCol = rColor.Interior.ColorIndex 
If SUM = True Then 
For Each rCell In rrange 
If rCell.Interior.ColorIndex = LCol Then 
vResult = WorksheetFunction.SUM (rCell, vResult) 
End If 
Next rCell 
Else 
For Each rCell In rrange 
If rCell.Interior.ColorIndex = LCol Then 
vResult = 1 + vResult 
End If 
Next rCell 
End If 
Color Function = vResult 
End Function

3 . Save the code and apply the following formula:

Number of colored cells : = colorfunction (A, B: C, FALSE) Add
Cells with color : = colorfunction (A, B: C, TRUE)

A: A is the cell with the background color you want to count and add.

B: C: is the range of cells you want to count and add.

4 . Please see the following screenshot. Enter the formula = colorfunction (A1, A1: D7, FALSE) to calculate the yellow cells. And use the = colorfunction (A1, A1: D7, TRUE) function to add yellow cells. See screenshot:

5 . If you want to count and add other colored cells, repeat step 4. You get the following result:

Count / Add With Kutools for Excel And Do Other Calculations With Colored Cells

With the user-defined function above, you have to enter your formula, and if there are many different colors, this method is cumbersome and time-consuming. But with the Count by Color function of Kutools for Excel, you can create a report of colored cells very quickly. You can use color plots to count and add and calculate the mean and maximum, and minimum values of this range.

Kutools for Excel includes more than 120 convenient Excel tools. Unlimited trial for 30 days.

After installing Kutools for Excel, please do as follows:

1 . Select the region you want to use.

2 . Click Enterprise > Count by Color (see screenshot):

3 . In the Count by Color dialog box, all colors are counted. You can filter colored cells according to your needs. For example, you can count cells with a defined background in the default format (or conditional format). Proceed as follows:

(1) Click in the Color method box and choose Standard formatting (or Conditional formatting ) from the drop-down list;

(2) Click in the Count type box and select Background from the drop-down list.

4 . Then click Generate report, and a new spreadsheet with statistics will be created. See screenshot