Number of text cells by color

[PomDave]

Hi All,

Is there a formula/macro that can sum the number of cells in a column, or a range of columns, that are red and those that are black?

Rgds,
Dave R.

 

[shamimarita]

Hello PomDave,

Yes, this is possible with a small VBA user-defined function. Excel formulas cannot directly count cells by font color, so a macro/UDF is the best option.

Please try this VBA function:

Function CountTextByFontColor(rng As Range, colorCell As Range) As Long
    Dim cell As Range
    Dim countResult As Long
    
    For Each cell In rng
        If cell.Value <> "" And Not IsNumeric(cell.Value) Then
            If cell.Font.Color = colorCell.Font.Color Then
                countResult = countResult + 1
            End If
        End If
    Next cell
    
    CountTextByFontColor = countResult
End Function

Then use it like this in Excel:
=CountTextByFontColor(A1:A100,C1)

Here, A1:A100 is the range you want to check, and C1 should contain sample text formatted with the color you want to count, for example, red or black.

For example:
=CountTextByFontColor(A1:A100,C1)

counts text cells with the same font color as C1.
=CountTextByFontColor(A1:A100,D1)

counts text cells with the same font color as D1.

If you mean the cell fill/background color instead of the text/font color, replace this line:

If cell.Font.Color = colorCell.Font.Color Then

with:

If cell.Interior.Color = colorCell.Interior.Color Then

After adding or changing colors, press Ctrl + Alt + F9 to recalculate the workbook.