Sum/Count Cells By Fill Or Background Color in Excel

First open the Workbook in which you wish to count or sum cells by a fill color. Now go into the Visual Basic Editor via Tools>Macro>Visual Basic Editor (Alt+F11)and then, from within the Visual Basic Editor go  to Insert>Module to insert a standard module. Now, in this module, enter in the code as shown below;

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

 Dim rCell As Range

 Dim lCol As Long

 Dim vResult



''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com



'Sums or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

  

 lCol = rColor.Interior.ColorIndex



 If SUM = True Then

  For Each rCell In rRange

   If rCell.Interior.ColorIndex = lCol Then

    vResult = WorksheetFunction.SUM(rCell,vResult)

   End If

  Next rCell

 Else

  For Each rCell In rRange

   If rCell.Interior.ColorIndex = lCol Then

    vResult = 1 + vResult

   End If

  Next rCell

 End If



   ColorFunction = vResult

End Function

---

You can now use the custom function (ColorFunction) like;

=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The reason it will SUM in this example is because we have used TRUE as the last argument for the custom function.

To COUNT these cells that have the same fill color as cell $C$1 you could use:

=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument our function will automatically default to using FALSE.

Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell and re-enter the formula, or go to Edit>Replace and replace = with =, or use Ctrl+Alt+F9