2 Excel Functions/Formulas to Count/Sum Excel Cells by Color

Got any Excel/VBA Questions? Excel Help.

This UDF will sum a range of cells based on their fill colour

The Code:

Function SumColor(rColor As Range, rSumRange As Range)



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

'Written by Ozgrid Business Applications

'www.ozgrid.com



'Sums cells based on a specified fill color.

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

	Dim rCell As Range

	Dim iCol As Integer

	Dim vResult



	iCol = rColor.Interior.ColorIndex



		For Each rCell In rSumRange

			If rCell.Interior.ColorIndex = iCol Then

				vResult = WorksheetFunction.Sum(rCell) + vResult

			End If

		Next rCell



	SumColor = vResult

End Function

Or, this UDF will count a range of cells based on their fill color

The Code:

Function CountColor(rColor As Range, rSumRange As Range)



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

'Written by Ozgrid Business Applications

'www.ozgrid.com



'Counts cells based on a specified fill color.

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

Dim rCell As Range

Dim iCol As Integer

Dim vResult



iCol = rColor.Interior.ColorIndex



   For Each rCell In rSumRange

    If rCell.Interior.ColorIndex = iCol Then

         vResult = vResult + 1

    End If

   Next rCell



CountColor = vResult

End Function

To use these UDF's push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function (Shift+F3).

Use the Function as shown in the graphic example below.