Thread: How to have a dropdown with Colours as well as Count Cells having a colours?

With Excel2007 you can filter by a colour,so the simplest way would be to combine this with a SUBTOTAL Function. Otherwise to count or sum by colours you would need VBA.

This User Defined Function will work in all versions of Excel

Code:

Option Explicit
''''''''''''''''''''''''''''''''''''''
'Sums or counts cells based on a specified fill Colour.
'''''''''''''''''''''''''''''''''''''''
Function ColourFunction(rColour As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCl As Range
    Dim rRng As Range
    Dim lCol As Long
    Dim vResult

    lCol = rColour.Interior.ColorIndex

    For Each rCl In rRange
        If rCl.Interior.ColorIndex = lCol Then
            If rRng Is Nothing Then
                Set rRng = rCl
            Else: Set rRng = Union(rCl, rRng)
            End If
        End If
        vResult = WorksheetFunction.SUM(rCl, vResult)
    Next rCl
    If SUM = True Then
        vResult = Application.WorksheetFunction.SUM(rRng)
    Else: vResult = rRng.Cells.Count
    End If
    ColourFunction = vResult
End Function

Sub TestCount()
    MsgBox ColourFunction(Range("a2"), Range("A2:a4"))
End Sub
Sub testSum()
    MsgBox ColourFunction(Range("a2"), Range("A2:a4"), True)
End Sub

The attached example demonstrates both ways