Count Cells Based on Color

---

None of the Count functions in Excel allow you to count number of cells having same color. 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 COLORCOUNT(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 = 1 + vResult             End If         Next rCriteriaCell     Next rCell    COLORCOUNT = vResult End Function

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

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

---