Sum of Cells based on Color

---

Many times we end up color coding various cells in the worksheet and then wish to perform SUM (or any other artithmatic operation) on these cells based on color coding. However SUMIF function provided by Microsoft doesn't accept Color as a criteria. 

To achieve this, we will need to write a Custom function using VBA.

Follow the steps below:

• Open the workbook in which you wish to count the cells based on color.
• Go to Developer --> Visual Basic (Alt + F11)
• If you don’t have any Module available, then right click on Sheet 1 in Project window and insert Module.
• An alternative to above two steps is:
• Go to Developer --> Macros
• Provide Macro name in Macro window and click on Create.
• You are now in “Microsoft Visual Basic for Applications” window. Replace “Sub” with “Function”.
• In Module window, insert following code:

 

Function COLORSUM(range As range, criteria As range)     Dim rCell, rCriteriaCell As range     Dim vResult     For Each rCell In range         For Each rCriteriaCell In criteria             If rCell.Interior.ColorIndex = rCriteriaCell.Interior.ColorIndex Then                 vResult = WorksheetFunction.SUM(rCell, vResult)             End If         Next rCriteriaCell     Next rCell    COLORSUM = vResult End Function

You can now use this newly added custom function as follows:

• If you want to SUM values in cells matching a single color:
• =COLORSUM(B1:B10,A1) where B1:B10 is the range from which you want to pick up the cells having color specified in Criteria cell A1 to SUM.
• If you want to SUM values in cells matching multiple colors:
• =COLORSUM(B1:B10,A1:A2) where B1:B10 is the range from which to want to pick up the cells having color specified in Criteria range A1:A2 to SUM.

NOTE: You can modify this function to support any other arithmatic operation based on color of the cell. The only change you would need to do is to select correct WorksheetFunction.

---