Dave asks:

How difficult would it be to make it count colour alone (so not unique values) and / or use cell font colour or other features also (bold for example) ? Excel has some quick green (with green text) and red (with red text) preset formats that people often use in either conditional formats or by manual application and althought the unique values is very useful in a lot cases this is even more detailed than required for just a count of the formatted conditions. Awesome site by the way, it's been invaluable to me lately! Cheers..

Count cells by cell font properties (font color, cell color, bold or italic)

count cells by cell formatting

 

Custom function in cell D3:

=CountC(A1:A20,C3)

The user defined function counts how many times the font properties in cell C3 matches each cell in cell range A1:A20.

User defined function

Function CountC(rng As Range, Cell As Range)
Dim CellC As Range, ucoll As New Collection, i As Single
i = 0
For Each CellC In rng
    If CellC.Interior.Color = Cell.Interior.Color And _
    CellC.Font.Bold = Cell.Font.Bold And _
    CellC.Font.Italic = Cell.Font.Italic And _
    CellC.Font.ColorIndex = Cell.Font.ColorIndex Then
        i = i + 1
    End If
Next CellC
    CountC = i
End Function

 

Count unique distinct cells by cell font properties (font color, cell color, bold or italic)

count unique distinct cell values by cell formatting

Custom function in cell D3:

=CountC(A1:A20,C3)

The user defined function matches the font properties in cell C3 with each cell in cell range A1:A20, then counts unique distinct values in those matching cells.

User defined function

Function CountUDC(rng As Range, Cell As Range)
Dim CellC As Range, ucoll As New Collection
For Each CellC In rng
    If CellC.Interior.Color = Cell.Interior.Color And _
    CellC.Font.Bold = Cell.Font.Bold And _
    CellC.Font.Italic = Cell.Font.Italic And _
    CellC.Font.ColorIndex = Cell.Font.ColorIndex Then
        On Error Resume Next
            If Len(CellC) > 0 Then ucoll.Add CellC, CStr(CellC)
        On Error GoTo 0
    End If
Next CellC
CountUDC = ucoll.Count
End Function

 

Download excel *.xlsm file

Count values and unique distinct values by cell formating.xlsm