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.
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:
Good luck!