To sum by color in Excel 2013 (and previous versions) you must create a VBA Module. Don’t worry, it sounds scarier than it is. Follow these steps to create a new function called SumByColor to your workbook.
- Press ALT+F11 to open Visual Basic for Applications
- Click Insert | Module
-
Paste in the following code:
Function SumByColor(MyRange As Range, MyColor As Range)
SumByColor = 0
TheColor = MyColor.Interior.Color
For Each cell In MyRange
If cell.Interior.Color = TheColor Then
SumByColor = SumByColor + cell.Value
End If
Next cell
End Function
- Press CTRL+S to save your workbook.
- Close Microsoft Visual Basic for Applications
-
Use your new function in your workbook using this syntax:
=SumByColor(Range,Color)- Range is the Range of cells you want Summed.
- Color is a cell that contains the color you want summed.
- Example:
- To preserve this functionality in the workbook, you will need to save it as an “Excel Macro-Enabled Workbook“
helpful but it is rounding to the nearest whole number and i need it to be exact.
Check the formatting of the cell you have the formula in. This function doesn’t specify rounding. I tried it with data containing 6 characters after the decimal point and it summed the values accurately. Best of luck, Josh.