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
- Press CTRL+S to save your workbook.
- Close Microsoft Visual Basic for Applications
Use your new function in your workbook using this syntax:
- Range is the Range of cells you want Summed.
- Color is a cell that contains the color you want summed.
- To preserve this functionality in the workbook, you will need to save it as an “Excel Macro-Enabled Workbook“