Calculate by Cell Background Color in Excel

This article provides a clear overview of Calculate by Cell Background Color in Excel, with the main facts, useful context, and practical details organized for easy reading.

Example

The colors have corresponding values. Sum the values corresponding to the cell's background color.

Step 1: Press the key combination Alt + F11 to open VBA 's working window -> Go to Insert tab -> Module .

Step 2: Build the function to calculate the total by color (named SumByColor ). Enter the following code into the newly created Module:

Function SumByColor (cellColor As Range, rRange As Range) Dim tong As Long Dim mau_sac As Integer mau_sac = cellColor.Interior.ColorIndex For Each c In rRange If c.Interior.ColorIndex = mau_sac Then tong = WorksheetFunction.Sum (c, tong) End If Next c SumByColor = tong End Function 

Note: Remember the name of the function you just created.

Step 3: Go back to the Excel File to calculate and select the SumByColor function as shown below:

Step 4: The SumByColor function (cellColor, rRange) has 2 arguments that are the color value of the cell and the data range to be calculated -> Enter the statement as shown:

Step 5: Convert the cell address to absolute address, highlight the address area and press F4. (You should do this step to avoid errors).

Finally Copy the formula to the remaining cells -> Result: