Calculate by cell background color in Excel

The following article details how to sum data cells by cell background color in Excel by building a macro function to calculate the cell background color.

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

Calculate by cell background color in Excel Picture 1

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

Calculate by cell background color in Excel Picture 2

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

Calculate by cell background color in Excel Picture 3

 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:

Calculate by cell background color in Excel Picture 4

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:

Calculate by cell background color in Excel Picture 5

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

Calculate by cell background color in Excel Picture 6

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

Calculate by cell background color in Excel Picture 7

Good luck!