help with formula

Visual Basic.

Step 1: Create the function

Press Alt + F11

Insert → Module

------------------------------------- Paste this code: -------------------------------------

Function CountByColor(rng As Range, colorCell As Range) As Long

    Dim c As Range

    Dim count As Long

    For Each c In rng

        If c.Interior.Color = colorCell.Interior.Color Then

            count = count + 1

        End If

    Next c

    CountByColor = count

End Function

-------------------------------------Step 2: -------------------------------------

Use your function in Excel by inserting it in a field (example is currently only for 1 column from A1 to A100, B1 → cell with the color you want to count )

=CountByColor(A1:A100, B1)

Hellojjpbello​,

Excel cannot count cells by fill color with a built-in formula. Use one of these approaches:

VBA function – create CountColor(rng, colorCell) to return how many cells match a color. See Microsoft documentation 

VBA

Filter by color – Data → Filter → Filter by Cell Color → see count in status bar. See Microsoft documentation 

Font Color Cell

Conditional formatting – count using COUNTIF or COUNTIFS based on the condition, not the color