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.
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!
You should read it
- How to change spreadsheet color lines in Excel
- How to color the background, table color in Word, Excel
- Instructions to change the cell background color (Cell) in Excel
- Delete background colors and backgrounds in Excel
- Change color between different lines in Microsoft Excel
- How to automatically color rows and columns in Excel
- How to use the SUM function to calculate totals in Excel
- Format borders and background colors for tables in Excel
- Instructions to add background color to each cell in the table on Word
- How to use themes in Excel
- How to use the kernel function (PRODUCT function) in Excel
- Complete tutorial of Excel 2016 (Part 5): Basics of cells and ranges
Maybe you are interested
Difference between function and formula in Excel
8 little-known Excel functions that can save you a lot of work
How to use the NORMDIST function in Excel - Function that returns the distribution in Excel
Date functions in Excel, DAY, WEEKDAY, MONTH
How to use the SUMIF function in Excel to calculate the sum based on conditions
How to use the Round function in Excel to round numbers and process data