This video demonstrates how to count and sum based on the background colour of a cell. The solution uses custom VBA functions. Download the featured file here. Please note I cannot upload macro enable files to this website, so you will need to copy the code shown below.

Here are the custom VBA functions featured in the video:

Count red cells function

Function CountRedCells(Rge As Range) As Double

Dim CellInRge As Range

For Each CellInRge In Rge

    If CellInRge.Interior.Color = RGB(255, 0, 0) Then
        CountRedCells = CountRedCells + 1
    End If
    
Next CellInRge
    
End Function

Count coloured cells function

Function CountColouredCells(Rge As Range, Colour As Range) As Double

Dim CellInRge As Range

For Each CellInRge In Rge

    If CellInRge.Interior.Color = Colour.Interior.Color Then
        CountColouredCells = CountColouredCells + 1
    End If
    
Next CellInRge
    

End Function

Sum red products function

Function SumRedCells(Rge As Range, SumColPos As Byte) As Currency

Dim CellInRge As Range

For Each CellInRge In Rge

    If CellInRge.Interior.Color = RGB(255, 0, 0) Then
        SumRedCells = CellInRge.Offset(0, SumColPos - 1) + SumRedCells
    End If
    
Next CellInRge

End Function

Sum coloured cells function

Function SumColouredCells(Rge As Range, Colour As Range, SumColPos As Byte) As Currency

Dim CellInRge As Range

For Each CellInRge In Rge

    If CellInRge.Interior.Color = Colour.Interior.Color Then
        SumColouredCells = CellInRge.Offset(0, SumColPos - 1) + SumColouredCells
    End If
    
Next CellInRge

End Function

Code to get the formulas to automatically update when you change cell colour. This needs to be stored in the relevant worksheet object.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub

Posted by Blue Pecan Computer Training