Sum by cell color
To sum cells that have a specific color we need to create a new function in Excel, with the use of VBA, that can then be applied as any other Excel function
Example: Sum by cell color
Function SumByCellColor(Data As Range, CellRefColor As Range)
'declare a variable
Dim CellColor As Long
Dim CurrentCell As Range
Dim SumCell As Long
Dim CellColor As Long
Dim CurrentCell As Range
Dim SumCell As Long
CellColor = CellRefColor.Interior.ColorIndex
For Each CurrentCell In Data
If CellColor = CurrentCell.Interior.ColorIndex Then
SumCell = WorksheetFunction.Sum(CurrentCell, SumCell)
End If
SumCell = WorksheetFunction.Sum(CurrentCell, SumCell)
End If
Next CurrentCell
SumByCellColor = SumCell
End Function
ADDITIONAL NOTES
Note 1: This VBA code creates a new function that can be used to sum the number of cells that contain a specified color. The first part of the funding is associated with the Data range. The second part of the function is associated with the cell reference that contains the specific color that you want to sum for.
Note 1: This VBA code creates a new function that can be used to sum the number of cells that contain a specified color. The first part of the funding is associated with the Data range. The second part of the function is associated with the cell reference that contains the specific color that you want to sum for.
|
=SumByCellColor(B5:C13,E5)
|
This function was created in VBA, as per the VBA code above, that sums the values in each of the cells that are colored with the specified color in the selected range (B5:C13). The first part of the formula relates to the data range which you want to test for. The second part of the formula specifies which color you want to sum for. In this example cell (E5) is colored with the specific color that you want to sum for.
Note: for this function to work you must first create the VBA code, as per the VBA example and save the workbook as an Excel Macro-Enabled Workbook. |
EXPLANATION
To sum the number of cells that contain a specific color we first need to create a new function in VBA and write function in Excel.
To sum the number of cells that contain a specific color we first need to create a new function in VBA and write function in Excel.
FORMULA
=SumByCellColor(data_rng,specific_color)
=SumByCellColor(data_rng,specific_color)
ARGUMENTS
data_rng: Range of cells to test for colored cells.
specific_color: The color to sum for.