If you color the cells using conditional formatting then you can also use conditional formatting to count the colors.
If you color the cells manually then you will need VBA and a formula and will have to use 1 highlight color if summing them all.
1.Highlight a cell that will always remain highlighted to use as a reference.
2. Create a new Module in VBA and paste this code
Function SumByColor(InputRange As Range, ColorRange As Range) As Double
' returns the sum of each cell in the range InputRange that has the same
' background color as the cell in ColorRange
' example: =SumByColor($A$1:$A$20,B1)
' range A1:A20 is the range you want to sum
' range B1 is a cell with the background color you want to sum
Dim cl As Range, TempSum As Double, ColorIndex As Integer
' Application.Volatile ' this is optional
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempSum = 0
On Error Resume Next ' ignore cells without values
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex Then
TempSum = TempSum + cl.Value
End If
Next cl
On Error GoTo 0
Set cl = Nothing
SumByColor = TempSum
End Function
3. Paste this formula in the cell that will display the sum
Formula:
=SumByColor($A$1:$F$14,$J$1)
4. Adjust $J$1 to the cell that will always be highlighted with the color to look for.
5. Adjust $A$1:$F$14 to represent your range to sum.
If any additional cells highlighted after the formula is updated you will have to Press Ctrl+Alt+F9 (or press enter within the formula window) to update the total.
Bookmarks