OzGrid

Sum/Count Cells By Fill Or Background Color in Excel

Category: [Excel]  Demo Available

Sum/Count Cells By Fill Or Background Color in Excel

Got any Excel/VBA Questions? Free Excel Help.

Want to Sum or Count cells that have a specified Fill Color? This can be done with the aid of a Custom Function.  A custom function, also called a User Defined Function, is a function that we write ourselves using VBA (Visual Basic for Applications).  Here is how

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

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

'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

Try also to avoid the use of Application.Volatile as it will not help in this case and only slow down Excel's calculation time.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

stars (0 Reviews)