Count or Sum Cells That Have a Specified Fill Color
Using a bit of code, you can easily SUM or COUNT cells whose fill color was specified manually.
Every nowand then, it's convenient to SUM or COUNT cells that have a specified fill color that you or another user have set manually, as users often understand paint colors more readily than named ranges. To do this, first open the workbook where you want to COUNT or SUM cells by a fill color. Go into the VBE by selecting Alt/Option-F11 and then select Insert → Module to insert a standard module. In this module, type the following code:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult 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
Close the window to get back to your worksheet, and save your workbook.
Nowyou can use the custom function ColorFunction in formulas such as this:
=ColorFunction($C$1,$A$1:$A$12,TRUE)
to sum the values in the range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The function will sum in this example because you used TRUE as the last argument for the custom function.
To count the cells that have the same fill color as cell $C$1, you can use this:
=ColorFunction($C$1,$A$1:$A$12,FALSE)
or:
=ColorFunction($C$1,$A$1:$A$12)
By omitting the last argument, the function automatically defaults to using FALSE as the last argument. Nowyou easily can SUM or COUNT cells that have a specified fill color.
In this tutorial:
- Speed Up Code While Halting Screen
- Run a Macro at a Set Time
- Use CodeNames to Reference Sheets in Excel Workbooks
- Connect Buttons to Macros Easily
- Create a Workbook Splash Screen
- Excel Display a "Please Wait" Message
- Cell Ticked or Unticked upon Selection
- Count or Sum Cells That Have a Specified Fill Color
- Excel Calendar Control to Any Excel Workbook
- Password-Protect and Unprotect All Excel Worksheets